|
Extract phone number front block of text
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 |
| All times are GMT +1. The time now is 11:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com