# Extracting Phone & Fax Numbers In #Excel

Here’s how we can use MID, FIND, and IFERROR to extract phone and fax numbers from a list in Excel:

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:

=MID(A2,6,13)

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:

=MID(A2,FIND(“Fax”,A2)+5,13)

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:

=IFERROR(MID(A2,FIND(“Fax”,A2)+5,13),””)

Now we have a clean list of numbers to work from!

Happy Excelling!

### One Response to Extracting Phone & Fax Numbers In #Excel

1. ralou June 2, 2015 at 1:09 pm #

Hello,
I came across your excellent trick.
I am faced though with a different situation. My sister has her contacts on a sheet that goes like this (§ is for cell):
§John§Adams§School§ 13, South Bld 8489123456 7321654321 good looking