Address to multiple cell

[patv]

Good morning

I have a excel spreadsheet that has 100 lines and in 1 column there is a mailing address in a cell set with the normal format of 3 lines as below
Name
Address
City, State zip

Is there a way to take the one cell and split to a cell for each category?


Thank you

 

[JamesCanale]

If they are 'alt-enter'ing between lines, this will work:

MrExcelPlayground23.xlsx
	F	G	H	I
24	John Smith 100 Main Street Anytown, NY 10001	John Smith	100 Main Street	Anytown, NY 10001
Sheet23

Cell Formulas
Range	Formula
G24:I24	G24	=TEXTSPLIT(F24,CHAR(10))
Dynamic array formulas.

 

[patv]

If they are 'alt-enter'ing between lines, this will work:

MrExcelPlayground23.xlsx
	F	G	H	I
24	John Smith 100 Main Street Anytown, NY 10001	John Smith	100 Main Street	Anytown, NY 10001
Sheet23

Cell Formulas
Range	Formula
G24:I24	G24	=TEXTSPLIT(F24,CHAR(10))
Dynamic array formulas.

This did not seem to work it just put the formula in not sure exactly how the cell was created I belive they did a mailmerge

 

[JamesCanale]

If it's one line of text with a "wrap text" - taking it apart is much harder than you would think. Try increasing the width of the column a lot, does it revert to one long line?

My formula is looking for the 'alt-enter' that forces a line break in a cell - which is 'char(10)' - basically a tab. If there is no line breaks of any kind, handling addresses with formulas is never easy - names can have two or three words (or more or less), the same with address' and town names. I've never had great luck with that.

 

[Fluff]

it just put the formula in

If you mean you simply see the formula, change the cell format to General & then try the formula again.

 

[patv]

James
increasing the width does not do anything -- i attached a image

 

[patv]

All Fluff's suggestion worked with the exception there is a coma then a space after the city , what changes in the formula do I need to do to have the city and state and zipcode split ( There is also a space between state and zip)