Meraz Al Nahian
This article provides some basic ideas on how to remove parentheses from phone numbers in Excel. Suppose you want to save some phone numbers of some people without any parentheses in them.
I explained 5 methods so that you can remove the parentheses from mobile numbers in the simplest possible way. In the dataset, we have some guys and their corresponding phone numbers.
How to Remove Parentheses from Phone Numbers in Excel: 5 Ways
1. Removing Parentheses from Phone Numbers by Using Nested SUBSTITUTE Function
The easiest possible way to remove parentheses from phone numbers is to use the nested formula of the SUBSTITUTE function. Let’s discuss the process below.
Steps:
- Make a new column for mobile numbers without parentheses and type the following formula in cell D5.
=SUBSTITUTE(SUBSTITUTE(C5,"(",""),")","")
Here, the SUBSTITUTE function removes the parentheses in two steps. The nested SUBSTITUTE function removes the first parentheses, and the other removes the second parentheses.
- Now hit the ENTER button, and you will see the phone number in cell C5 become parentheses
- Use the Fill Handle to AutoFill the lower cells, and you will see all the phone numbers are removed from parentheses.
By following this simple formula, you can easily remove parentheses from phone numbers.
2. Using Excel MID and RIGHT Functions to Remove Parentheses from Phone Numbers
Another easy way to remove parentheses from mobile numbers is by using the MID and RIGHT functions. Let’s hop into the procedure.
Steps:
- First, make a new column for the parentheses-free mobile numbers and type the following formula in cell D5.
=MID(C5,2,3)&RIGHT(C5,9)
In this formula, 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 (&) helps these two strings of characters to get together.
- Now press ENTER, and you will see the mobile number in cell C5 is removed from the parentheses.
- Use the Fill Handle to AutoFill the lower cells, and you will see all the mobile numbers become parentheses
This is another simple method to remove parentheses from mobile numbers.
Read More: [Solved!]: Excel Phone Number Format Not Working
3. Applying Combined Functions to Remove Parentheses from Phone Numbers
We can also remove parentheses from cell phone numbers by combining the MID and RIGHT functions into the SUBSTITUTE function. I’m explaining in the following description.
Steps:
- Make a new column for the parentheses-free mobile numbers and type the following formula in cell D5.
=SUBSTITUTE(C5,C5,MID(C5,2,3)&RIGHT(C5, 9))
Formula breakdown
Here, 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“
Finally, we got the phone number 212 222-3246.
- Hit ENTER, and you will see no parentheses in the phone number.
- Now use the Fill Handle to AutoFill the lower cells, and you will see all the phone numbers are now parentheses-free.
By following this approach, you can terminate parentheses from phone numbers in Excel.
Similar Readings
- How to Write Phone Number in Excel
- How to Format Phone Number with Dashes in Excel
- How to Format Phone Number with Country Code in Excel
4. Utilizing Combined Functions into the REPLACE Function to Remove Parentheses from Phone Numbers
Removing parentheses from phone numbers can also be done by combining the LEFT, RIGHT, and FIND functions into the REPLACE function. This is going to be a little bit messy. Let’s just cut to the chase.
Steps:
- Make a new column for the parentheses-free phone numbers and type the following formula in cell D5.
=REPLACE(LEFT(C5,4),1,FIND((",C5),"")&REPLACE(RIGHT(C5,10),1,FIND(")",RIGHT(C5,10)),"")
Formula Breakdown
Here, we have nested LEFT, RIGHT and FIND functions into 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
Finally, we get the mobile number free from parentheses.
- Press the ENTER key to see the output in cell D5.
- Use the Fill Handle to AutoFill the lower cells.
Thus, you can remove parentheses from the phone numbers you want.
Read More: Excel Formula to Change Phone Number Format
5. Using Replace Command from Find & Select Feature
If you don’t feel comfortable using the formula, you can remove parentheses using the Replace command from the Find & Select ribbon. Let’s follow my process below.
Steps:
- Select the range C5:C11, then go to Home >> Find & Select >> Replace
- A dialog box will appear. Type first parentheses “(“ in Find what
- As we wanted to remove the parentheses, we kept empty the Replace with
- Then click on Replace All.
- A message box will appear. Just click OK and Close the dialog box.
You will see all the first parentheses gone from the phone numbers.
- To remove the second parentheses, open the Replace command again and type 2nd parentheses “)” in the Find what section.
- Keep the “Replace with” section empty.
- Click on Replace All and also click on OK in the message box.
- After that, Close the window.
You will now see all the phone numbers without any parentheses.
This is a very easy method to remove parentheses from phone numbers.
Practice Section
The following image is the dataset that we have worked on to explain the methods to remove parentheses from phone numbers. You can practice these methods on your own.
Download Practice Workbook
Conclusion
The article emphasizes 5 easy methods of how to remove parentheses from phone numbers in Excel. By following these methods, you can remove parentheses not only from phone numbers but also from any other strings or texts. I hope this article will be useful for you to solve similar kinds of problems. If you have any easier methods, ideas or feedback, please leave them in the comment box. This will help me enrich my upcoming articles.
