Extract phone number front block of text

T

Tech

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'
like to do is something like....

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

In the next column do...

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

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 an
didn't get very far
 
S

swatsp0p

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
 
M

Michael

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
 
S

swatsp0p

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

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

The result would be, e.g.

952 123-4567

Good Luck

Bruc
 
Top