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

J

Joe

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?
 
R

Rick Rothstein \(MVP - VB\)

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
 
M

muddan madhu

Select the column & then

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

JohnL

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

Rick Rothstein \(MVP - VB\)

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
 
Top