Extract phone number front block of text

**Tech** · 08-04-2005, 09:25 AM

I have a webpage that lists business names, address, phone number, etc. There is no definite pattern to how they entered the text. What I'd like to do is something like....

Search cell A1 for "-", return the 3 characters to the left of the "-".

In the next column do...

Search cell A1 for "-", return the 4 characters to the right of the "-".

Then I can concatenate the two and add in the area code.

TIA guys/gals.

PS. I tried searching but didn't know exactly what to search for and didn't get very far.

**swatsp0p** · 08-04-2005, 09:50 AM

HI, Tech. Where will you get the Area Code that you will 'add in'?

Also, how will you handle single cell entries that also use the hyphen in a business name (e.g. Allis-Chalmers) or a city (e.g. Wilkes-Barre, PA) or a 9 digit ZIP code (e.g. 90210-1234)?

Things to ponder....

Bruce

**Tech** · 08-04-2005, 09:56 AM

The area code is the same for every number (in this case).

Michael · 08-04-2005, 11:05 AM

For the left three numbers, =MID(A1,(SEARCH("-",A1)-3),3). For the right
four numbers, =MID(A1,(SEARCH("-",A1)+1),4). If these formula are in B1 and
C1, to combine the numbers with the area code, "925 "&B1&"-"&C1. HTH
--
Sincerely, Michael Colvin

"Tech" wrote:

>
> I have a webpage that lists business names, address, phone number, etc.
> There is no definite pattern to how they entered the text. What I'd
> like to do is something like....
>
> -Search cell A1 for "-", return the 3 characters to the left of the
> "-".-
>
> In the next column do...
>
> -Search cell A1 for "-", return the 4 characters to the right of the
> "-".-
>
> Then I can concatenate the two and add in the area code.
>
> TIA guys/gals.
>
>
> PS. I tried searching but didn't know exactly what to search for and
> didn't get very far.
>
>
> --
> Tech
> ------------------------------------------------------------------------
> Tech's Profile: http://www.excelforum.com/member.php...o&userid=15107
> View this thread: http://www.excelforum.com/showthread...hreadid=392890
>
>

**Tech** · 08-08-2005, 09:49 AM

Worked great! Thanks for your help!

**swatsp0p** · 08-08-2005, 10:02 AM

Of course, you could combine Michael's formula into one cell, as such:

in B1 ="925 "&MID(A1,(SEARCH("-",A1)-3),3)&"-"&MID(A1,(SEARCH("-",A1)+1),4)

The result would be, e.g.

952 123-4567

Good Luck

Bruce