Tricky Extraction Problem

I have a list of businesses in Excel, but all the data for each business is in one cell. For example:

"Best Western Hospitality House Motel 532 N Market Street Redding CA 96003-3610"

"Comfort Suites San Clemente 3701 South El Camino Real San Clemente CA 92672"

I want to split the data into separate cells:
Business Name, Address, City, State, Zip Code should each be extracted to a separate cell.


Any suggestions on how to do this? Also, I would like to remove the remove the name of the city from the name of the business if it is there. For example, I would like "Comfort Suites" for business name, not "Comfort Suites San Clemente"

Thanks in advance.

Originally Posted by Spreadsheet

I have a list of businesses in Excel, but all the data for each business is in one cell. For example:

"Best Western Hospitality House Motel 532 N Market Street Redding CA 96003-3610"

"Comfort Suites San Clemente 3701 South El Camino Real San Clemente CA 92672"

I want to split the data into separate cells:
Business Name, Address, City, State, Zip Code should each be extracted to a separate cell.


Any suggestions on how to do this? Also, I would like to remove the remove the name of the city from the name of the business if it is there. For example, I would like "Comfort Suites" for business name, not "Comfort Suites San Clemente"

Thanks in advance.

Based on what you have, the State and Zip Code will be fairly easy. The rest is going to be rather difficult. (I'm assuming all Zip Codes are similiar to your examples and don't have any spaces in them instead of dashes)

For the Zip Code, use:
=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

For the State, use: (I'm assuming they're all 2 characters)
=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,2)

The problem for the rest is without you deciding for each line, it's hard to determine in a function where the name ends and the address begins, and the address ends and the city begins. (In your examples, the city is one word in the first one, but two words in the second)

It is possible to split the entire string out so that each word is in one column, if that would make things any easier for you.

You can do this by highlighting the column, choosing Data -> Text to Columns ...
Choose Delimited and Next. Choose Space as your delimiter and hit Next. I'd then highlight every column (you can do this by choosing the first column, then holding down Shift and choosing the last column) and change the Column data format to Text. Choose the Destination (by default it overwrites your original column). Hit Finish.

Sorry I can't help you much more than that.

Scott

Thanks Scott. The Zipcode and State have been extracted nicely. Is there not any way to extract every thing from the cell that occurs before the first number? This would enable extraction of the business name.

Originally Posted by Spreadsheet

Thanks Scott. The Zipcode and State have been extracted nicely. Is there not any way to extract every thing from the cell that occurs before the first number? This would enable extraction of the business name.

The best I can give you is the following:

=LEFT(A1,MIN(IF(ISERROR(FIND(RIGHT(ROW(A1:A10),1),A1)),"",FIND(RIGHT(ROW(A1:A10),1),A1)))-2)
Entered with CTRL+SHIFT+ENTER.

To get the position of the first number, it's just:
=MIN(IF(ISERROR(FIND(RIGHT(ROW(A1:A10),1),A1)),"",FIND(RIGHT(ROW(A1:A10),1),A1)))
Entered with CTRL+SHIFT+ENTER.

Hope that helps,
Scott

Thanks Scott,

That did work - except for businesses with numbers in their name.

Hi Spreadsheet

You're not going to get any closer than Maistrye's answer gets you. Just get used to the fact that if a business name has a number in it you'll have to do it manually.

DominicB