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

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=392890

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


--
Tech
------------------------------------------------------------------------
Tech's Profile: http://www.excelforum.com/member.php...o&userid=15107
View this thread: http://www.excelforum.com/showthread...hreadid=392890

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

Worked great! Thanks for your help! :)


--
Tech
------------------------------------------------------------------------
Tech's Profile: http://www.excelforum.com/member.php...o&userid=15107
View this thread: http://www.excelforum.com/showthread...hreadid=392890

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=392890

Glad it worked. Thanks for the feedback.
--
Sincerely, Michael Colvin


"Tech" wrote:


Worked great! Thanks for your help! :)


--
Tech
------------------------------------------------------------------------
Tech's Profile: http://www.excelforum.com/member.php...o&userid=15107
View this thread: http://www.excelforum.com/showthread...hreadid=392890