Text to multiple columns

**turfbug** · 12-04-2013, 05:08 PM

I have some data that I am trying to split into multiple columns. In each cell I have the following

Address 1
City State ZIP
Country

City State and Zip are not separated by commas and not all fields have a "country" line. Each line is separated by a carriage return.

I want the output to be

Address | City | State | Zip | Country(when applicable)

I have tried a few different things, but no success.

I am using Office for Mac 2011.

FWIW - It is all the data in column f (starting in row 6)

Thanks in advance.

**FDibbins** · 12-04-2013, 05:54 PM

Hi and welcome to the forum

I have tried a few different things, but no success.

Would 1 of those things be Text2Columns? Is so, what were the results? If not, give it a try. What other things have you tried?

**turfbug** · 12-04-2013, 06:05 PM

I tried text2columns but couldn't get multiple columns and the standard t2c couldn't deal with the carriage return.
I tried copying and pasting into a plain text editor and pasting it back in (paste special => transform) which would eventually get everything on one line to work with but then got stuck from there.

I tried using the "other" in T2C for the carriage return using variations of ALT+0010 to no avail.

I feel like i am back at square 1