Extracting Phone Numbers

  • Thread starter Thread starter Jim Berglund
  • Start date Start date
J

Jim Berglund

I have many lines of data in which each includes a telephone number. The
lines are not consistent in text or spacing. The number can have either a
(403) area code or a (587) area code.

I want to extract the number and have tried the following, which results in
a #VALUE!

=IF(MID(A2,(FIND("(587)",A2)),14)="#VALUE!",MID(A2,(FIND("(403)",A2)),14),MID(A2,(FIND("(587)",A2)),14))

If I use =(MID(A2,(FIND("(587)",A2)),14) on the lines that have a 587 area
code, it works. 'Same for the 403 code.

I need them to work together.

Any ideas on getting this to work - or any alternative approaches would be
appreciated.

Jim Berglund
 
I have many lines of data in which each includes a telephone number. The
lines are not consistent in text or spacing. The number can have either a
(403) area code or a (587) area code.

I want to extract the number and have tried the following, which results in
a #VALUE!

=IF(MID(A2,(FIND("(587)",A2)),14)="#VALUE!",MID(A2,(FIND("(403)",A2)),14),MID(A2,(FIND("(587)",A2)),14))

If I use =(MID(A2,(FIND("(587)",A2)),14) on the lines that have a 587 area
code, it works. 'Same for the 403 code.

I need them to work together.

Any ideas on getting this to work - or any alternative approaches would be
appreciated.

Jim Berglund
Click to expand...

I note that your approach assumes that all of the phone numbers are in a fixed
format of (nnn) nnn-nnnn (the "-" could be anything) and can occur anyplace in
the string.

If that is the case, then this formula should work:

=MID(A1,INDEX(FIND({"(403","(587"},A1),MATCH(
TRUE,ISNUMBER(FIND({"(403","(587"},A1)),0)),14)

However, if there is variability in the format of the phone numbers, then a
different approach would be preferable.

--ron
 
This Worked. Thanks a lot!
Jim

Ron Rosenfeld said:
I note that your approach assumes that all of the phone numbers are in a
fixed
format of (nnn) nnn-nnnn (the "-" could be anything) and can occur
anyplace in
the string.

If that is the case, then this formula should work:

=MID(A1,INDEX(FIND({"(403","(587"},A1),MATCH(
TRUE,ISNUMBER(FIND({"(403","(587"},A1)),0)),14)

However, if there is variability in the format of the phone numbers, then
a
different approach would be preferable.

--ron
Click to expand...
 
Back
Top