How to Remove Parentheses from Phone Numbers in Excel

Consider the following dataset with some people and their phone numbers with parentheses. We’ll remove the parentheses.

How to Remove Parentheses from Phone Numbers in Excel: 5 Ways

Method 1 – Removing Parentheses from Phone Numbers by Using a Nested SUBSTITUTE Function

Steps:

Make a new column for mobile numbers without parentheses and use the following formula in cell D5.

=SUBSTITUTE(SUBSTITUTE(C5,"(",""),")","")

The SUBSTITUTE function removes the parentheses in two steps. The nested SUBSTITUTE function removes the first parenthesis, and the other removes the second parenthesis.

Hit the Enter button.

Use the Fill Handle to AutoFill down.

Method 2 – Using Excel MID and RIGHT Functions to Remove Parentheses from Phone Numbers

Steps:

Make a new column for the parentheses-free mobile numbers and use the following formula in cell D5.

=MID(C5,2,3)&RIGHT(C5,9)

The MID function returns the characters from the 2nd to 4th position of the mobile number in cell C5 and the RIGHT function returns 9 characters from the right side of the mobile number as mobile numbers follow a pattern. The ampersand (&) joins these two strings of characters.

Hit Enter.

Use the Fill Handle to AutoFill down.

Method 3 – Applying Combined Functions to Remove Parentheses from Phone Numbers

Steps:

Make a new column for the parentheses-free mobile numbers and use the following formula in cell D5.

=SUBSTITUTE(C5,C5,MID(C5,2,3)&RIGHT(C5, 9))

Formula breakdown

We have nested MID and RIGHT functions in the SUBSTITUTE functions to remove parentheses from phone numbers.

MID(C5,2,3) —-> returns numbers from 2nd to 4th position in cell C5.
- Output: “212”.
RIGHT(C5, 9) —-> returns 9 characters from the right side of the phone number in cell C5.
- Output: “ 222-3246”.
MID(C5,2,3)&RIGHT(C5, 9) —-> reduced to
- Output: “212 222-3246“
SUBSTITUTE(C5,C5,MID(C5,2,3)&RIGHT(C5, 9)) —-> becomes
SUBSTITUTE(C5,C5,“212 222-3246“) —-> turns into
- Output: “212 222-3246“

We got the phone number 212 222-3246.

Hit Enter.

Use the Fill Handle to AutoFill down.

Method 4 – Utilizing Combined Functions and the REPLACE Function to Remove Parentheses from Phone Numbers

Steps:

Make a new column for the parentheses-free phone numbers and use the following formula in cell D5.

=REPLACE(LEFT(C5,4),1,FIND((",C5),"")&REPLACE(RIGHT(C5,10),1,FIND(")",RIGHT(C5,10)),"")

Formula Breakdown

We have nested LEFT, RIGHT and FIND functions in the REPLACE function to remove parentheses from the phone numbers in column C.

LEFT(C5,4) —-> returns
- Output: “(212”
FIND(“(“,C5) —-> returns
- Output: 1
REPLACE(LEFT(C5,4),1,FIND(“(“,C5),””) —-> becomes
REPLACE(“(212“,1,1,””) —-> returns
- Output: “212“
RIGHT(C5,10) —-> returns
- Output: “) 222-3246“
FIND(“)”,RIGHT(C5,10)) —-> becomes
FIND(“)”, “) 222-3246“) —-> returns
- Output: 1
REPLACE(RIGHT(C5,10),1,FIND(“)”,RIGHT(C5,10)),””) —-> reduced to
REPLACE(“) 222-3246“,1,1,””) —-> returns
- Output: ” 222-3246″
=REPLACE(LEFT(C5,4),1,FIND(“(“,C5),””)&REPLACE(RIGHT(C5,10),1,FIND(“)”,RIGHT(C5,10)),””) —-> becomes
=“212” & “ 222-3246” —-> returns
- Output: 212 222-3246

We get the mobile number without parentheses.

Press the Enter key to see the output in cell D5.

Use the Fill Handle to AutoFill down.

Method 5 – Using the Replace Command from the Find & Select Feature

Steps:

Select the range C5:C11, then go to Home, choose Find & Select, and pick Replace

A dialog box will appear. Put an opening parenthesis “(“ in Find what.
Keep the Replace with box empty.
Click on Replace All.

A message box will appear. Click OK and Close the dialog box.

You will see all the first parentheses gone from the phone numbers.

Open the Replace command again and put a closing parenthesis “)” in the Find what section.
Keep the Replace with section empty.
Click on Replace All and click on OK in the message box.
Close the window.

You will now see all the phone numbers without any parentheses.

Practice Section

The download file contains the dataset we have worked on to explain the methods to remove parentheses from phone numbers. You can practice these methods on your own.