Here’s how we can use MID, FIND, and IFERROR to extract phone and fax numbers from a list in Excel:
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.
I have a list of phone and fax numbers that I want to pull out to create a clean list. There is some consistency in that all the phone numbers begin with “Tel: ” and all the fax numbers begin with “Fax: “. However, as you can see, there is an inconsistency in how many spaces are between the phone and fax, and some don’t even have a fax number:
For the phone number, we are going to use the MID function. The MID function returns the characters from the middle of a string given a starting position and length. In our case, the starting position is always 6 (that is where the first “(” is), and the phone numbers are all 13 characters.
So, our formula will be:
For the fax number, we have to add the FIND function to determine the starting point for the MID function, since the starting point varies due to the different number of spaces between the two numbers. We can use FIND since all the listings have “Fax:” consistently and FIND is case sensitive. If there were variations, we would use SEARCH instead.
Our formula here will be:
The FIND function returns the position where “Fax” starts, but in our case, we want to start at the “(“, so we will add 5 to that position. We still will draw out 13 characters.
The only issue left is to get rid of the #VALUE! error where there was no Fax number. To do this, we can wrap the MID function in an IFERROR function, like this:
Now we have a clean list of numbers to work from!