how can I make info in one cell appear in 3 different columns?
 

I'd like to take something like this out of one cell:

White Plains, New York 10604

and make it appear in 3 different columns, is it possible?

how can I make info in one cell appear in 3 different columns?
 

Assuming the city is always separated from the state by a coma space and the
the zip code is always a 5-digit number, then if your entry is in A1, put
the following formulas in B1, C1 and D1...

B1: =LEFT(A1,FIND(",",A1)-1)

C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),D1,""))

D1: =RIGHT(A1,5)

Rick


"Joe" wrote in message
...
I'd like to take something like this out of one cell:

White Plains, New York 10604

and make it appear in 3 different columns, is it possible?

how can I make info in one cell appear in 3 different columns?
 

Select the column & then

Go to Data | text to columns | Click Fixed Width | next | finish



On Apr 20, 8:26*am, Joe wrote:
I'd like to take something like this out of one cell:

White Plains, New York 10604

and make it appear in 3 different columns, is it possible?

how can I make info in one cell appear in 3 different columns?
 

Rick,
Regarding the equation to extract the ZIP code, how would you retain a
leading zero as in Attleboro, MA 02703?
Thanks
John L

"Rick Rothstein (MVP - VB)" wrote:

Assuming the city is always separated from the state by a coma space and the
the zip code is always a 5-digit number, then if your entry is in A1, put
the following formulas in B1, C1 and D1...

B1: =LEFT(A1,FIND(",",A1)-1)

C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),D1,""))

D1: =RIGHT(A1,5)

Rick


"Joe" wrote in message
...
I'd like to take something like this out of one cell:

White Plains, New York 10604

and make it appear in 3 different columns, is it possible?

how can I make info in one cell appear in 3 different columns?
 

Format Column D as Special/Zip Code would be one way (although on my system,
the leading zero is preserved directly from the formula with the cell
Formatted as General).

Rick


"JohnL" wrote in message
...
Rick,
Regarding the equation to extract the ZIP code, how would you retain a
leading zero as in Attleboro, MA 02703?
Thanks
John L

"Rick Rothstein (MVP - VB)" wrote:

Assuming the city is always separated from the state by a coma space and
the
the zip code is always a 5-digit number, then if your entry is in A1, put
the following formulas in B1, C1 and D1...

B1: =LEFT(A1,FIND(",",A1)-1)

C1: =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&",",""),D1,""))

D1: =RIGHT(A1,5)

Rick


"Joe" wrote in message
...
I'd like to take something like this out of one cell:

White Plains, New York 10604

and make it appear in 3 different columns, is it possible?