I need to breakup an address in a cell...............

T

Tony

What is the formula for breaking up the contents of a cell into multiple cells.

A1
Detroit, MI 48089

A1 B2 B3
Detroit MI 48089

Thanks for the help...........

Tony
 
K

Kassie

Hi

Select the cell, then click on Data|Text to Columns, Select delimited and
Space as the delimiter, and Finish
 
G

Gary's Student

The feature you are looking for is called Text to Columns. Just select the
cell(s) and pull-down:

Data > Text to Columns... and let the Wizard guide you.

Good Luck
 
D

David Hepner

Try this:

in cell B1 - =LEFT(A1,FIND(",",A1,1)-1)

in cell B2 - =MID(A1,LEN(B1)+3,2)

in cell B3 - =RIGHT(A1,5)
 
M

Mike

If you have thousands of them I would dump them into Word. Convert table to
text. replace all commaspace with just comma. shoot I was going to tell
you to replace all spaces with commas but that place commas with town names.
Then convert text back to table and dump it into excel.
For those towns with spaces. it would push the zipcode into a separate
column. Sort by that column and make repairs to that area.
 
J

James W.

Here's a couple of functions to get you started...

City: =LEFT(A1,SEARCH(",",A1,1)-1)
State: =MID(A1,SEARCH(",",A1,1)+2,2)
Zip: =RIGHT(A1,5) 'if only 5 characters

hth
James
 
Top