3 Ways To Remove Parentheses From Phone Numbers In Excel

Intro

Master phone number formatting in Excel with 3 easy methods to remove parentheses. Learn how to clean up phone numbers, remove unwanted characters, and standardize formatting using formulas, find and replace, and text to columns features. Improve data quality and streamline phone number management in your Excel spreadsheets.

Phone numbers with parentheses can be a hassle to work with in Excel, especially when you need to perform calculations or data analysis. Fortunately, there are several ways to remove parentheses from phone numbers in Excel. In this article, we'll explore three methods to help you achieve this.

Understanding the Problem

Phone Numbers in Excel

Phone numbers with parentheses can appear in various formats, such as (123) 456-7890 or (123) 456-789-012. These parentheses can make it challenging to work with phone numbers in Excel, especially when you need to use them in formulas or data analysis.

Method 1: Using the SUBSTITUTE Function

SUBSTITUTE Function in Excel

One of the simplest ways to remove parentheses from phone numbers in Excel is by using the SUBSTITUTE function. This function replaces a specified character or text string with another character or text string.

To use the SUBSTITUTE function, follow these steps:

  1. Select the cell where you want to display the phone number without parentheses.
  2. Type the formula: =SUBSTITUTE(A1,"(","")
  3. Press Enter.

This formula will replace the left parenthesis in cell A1 with nothing, effectively removing it. If you want to remove the right parenthesis as well, you can modify the formula to: =SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")

How to Apply the SUBSTITUTE Function to Multiple Cells

If you have a range of cells with phone numbers that you want to remove parentheses from, you can apply the SUBSTITUTE function to multiple cells using the following steps:

  1. Select the range of cells that you want to apply the formula to.
  2. Type the formula: =SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")
  3. Press Ctrl+Enter.

This will apply the formula to all selected cells, removing parentheses from each phone number.

Method 2: Using the REPLACE Function

REPLACE Function in Excel

Another way to remove parentheses from phone numbers in Excel is by using the REPLACE function. This function replaces a specified character or text string with another character or text string.

To use the REPLACE function, follow these steps:

  1. Select the cell where you want to display the phone number without parentheses.
  2. Type the formula: =REPLACE(A1,2,1,"")
  3. Press Enter.

This formula will replace the left parenthesis in cell A1 with nothing, effectively removing it. If you want to remove the right parenthesis as well, you can modify the formula to: =REPLACE(REPLACE(A1,2,1,""),5,1,"")

How to Apply the REPLACE Function to Multiple Cells

If you have a range of cells with phone numbers that you want to remove parentheses from, you can apply the REPLACE function to multiple cells using the following steps:

  1. Select the range of cells that you want to apply the formula to.
  2. Type the formula: =REPLACE(REPLACE(A1,2,1,""),5,1,"")
  3. Press Ctrl+Enter.

This will apply the formula to all selected cells, removing parentheses from each phone number.

Method 3: Using Text to Columns Feature

Text to Columns Feature in Excel

If you have a large dataset with phone numbers that contain parentheses, you can use the Text to Columns feature in Excel to remove the parentheses. This feature allows you to split text into separate columns based on a specified delimiter.

To use the Text to Columns feature, follow these steps:

  1. Select the range of cells that contain the phone numbers with parentheses.
  2. Go to the Data tab in the Excel ribbon.
  3. Click on the Text to Columns button.
  4. In the Convert Text to Columns Wizard, select the Delimited Text option and click Next.
  5. In the next step, select the Parentheses checkbox and click Finish.

This will split the phone numbers into separate columns, removing the parentheses.

How to Apply the Text to Columns Feature to Multiple Cells

If you have a range of cells with phone numbers that you want to remove parentheses from, you can apply the Text to Columns feature to multiple cells using the following steps:

  1. Select the range of cells that you want to apply the feature to.
  2. Go to the Data tab in the Excel ribbon.
  3. Click on the Text to Columns button.
  4. In the Convert Text to Columns Wizard, select the Delimited Text option and click Next.
  5. In the next step, select the Parentheses checkbox and click Finish.

This will apply the feature to all selected cells, removing parentheses from each phone number.

Phone Number Formatting Image Gallery

Phone Number Formatting Example 1
Phone Number Formatting Example 2
Phone Number Formatting Example 3
Phone Number Formatting Example 4
Phone Number Formatting Example 5
Phone Number Formatting Example 6
Phone Number Formatting Example 7
Phone Number Formatting Example 8
Phone Number Formatting Example 9
Phone Number Formatting Example 10

Removing parentheses from phone numbers in Excel can be a simple task using the methods outlined above. Whether you choose to use the SUBSTITUTE function, REPLACE function, or Text to Columns feature, you can easily remove parentheses from phone numbers and format them to your desired format.

We hope this article has been helpful in showing you how to remove parentheses from phone numbers in Excel. If you have any questions or need further assistance, please don't hesitate to comment below.

Jonny Richards

Love Minecraft, my world is there. At VALPO, you can save as a template and then reuse that template wherever you want.