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:

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:

ph1

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)

ph2

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)

ph3

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),””)

ph4

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

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

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
    §Mary§Blue§old-school§ 235 North west Road (bad hood) cel. 8476386458 7854623498
    §Nick§Glued§from Tania§ —-???? 8434768256 – 8455876453 6548376231 will call him

    Meaning in the fourth cell on every row there are ONLY two constants:
    1. ALL tel numbers have 10 digits
    2. The mobile numbers ALL start with 84 (sometimes there are 2 mobile numbers or 2 land-lines)

    So… Is there a way to have all the numbers extracted in columns? One for mobiles and one for land-lines? Tricky….

    Thank you in advance
    ralou

Leave a Reply