In this tutorial we are going to see how we can use nested SUBSTITUTE functions, along with TRIM, RIGHT, FIND and LEN functions to convert a messy list of phone numbers into a neatly formatted group.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
So here we have a list of phone numbers with leading text and various characters and spaces within those numbers:
We want to convert them to just a series of numbers, then using a Special Excel format, put them in a phone number style like this:
The first step is to use the RIGHT, LEN, and FIND functions to strip off the leading text. Then wrap that formula in the TRIM function which will remove any leading or trailing SPACES plus reduce any extraneous spaces in between any “words” to just one. The formula we will use is:
And here is the result of that formula:
Now we are going to use that formula as the basis for our nested SUBSTITUTE formulas. The syntax for the SUBSTITUTE function is:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
The TRIM formula above will be used for the “text” argument, and we will then put in the character we want to remove in double quotes for the “old_text” and just double quotes for the “new_text”. For example, to get rid of the period, the formula will be:
Now it’s just a matter of adding more SUBSTITUTE functions, using the previous one as the “text” for the subsequent one, until we’ve built the following formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(A2,LEN(A2)-FIND(” “,A2))),” “,””),”.”,””),”(“,””),”)”,””),”-“,””)
Take a few minutes and watch the video so you can see how I keep adding more SUBSTITUTE functions to finally get the phone numbers to just the 10 digits.
I then multiplied the formula times 1 (*1) which converts the text formatted numbers into the number format.
The last step is to then format those numbers using a Special format Excel has for phone numbers. It can be found here:
And now we have our phone numbers in a more typical and useful format!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!