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

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

Hi

Select the cell, then click on Data|Text to Columns, Select delimited and
Space as the delimiter, and Finish
--
ve_2nd_at. Randburg, Gauteng, South Africa


"Tony" wrote:

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

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
--
Gary's Student


"Tony" wrote:

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

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)

"Tony" wrote:

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

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.

"Tony" wrote:

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

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

"Tony" wrote:

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