18
Excel Formulas to Clean & Reformat Telephone Numbers ~ Simple Tips!!
Suppose, you have a list of telephone numbers that are formatted with various formatting. Now, you want to clean up all the phone number formatting and reformat them with specific telephone numbers. How can use solve this problem? In this article, I will show the simple trick to achieve this job. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
- Use the below formula to convert the numbers into specified telephone number format.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”(“,””),”)”,””),”-“,””),” “,””),”.”,””)+0
- SUBSTITUTE – In Excel, the SUBSTITUTE function can replace existing text with new text in a text string when you want to replace text based on its content, not position.
- A1 – It represents the input value.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
Refer to the below example image.
- First, we will enter the input values in Column B.
- Here we are going to reformat the telephone numbers.
- Select any cell and apply the above-given formula.
- Finally, press ENTER to get the result out.
Here, we have described the simple formulas to clean and reformat the telephone numbers in Excel. Mention your queries in the comment box below.
Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **
18