single cell of data, multiple lines [address] - transposed to rows

[TJMMARTELLA]

I have single cell containing an address [all information in one cell] - I would like to transpose the information so that first line is in it's own column, followed by address in own column, city in own column, state, etc... thanks for any help.

 

[Andrew Poulsom]

Can you post some sample data? If the lines of the address are separated by a line feed character, you can use Data|Text To Columns, select Delimited and at Step 2 check Other and with your cursor in the TextBox hold down Alt and type 010 on the numeric keypad.

 

[TJMMARTELLA]

WOW. That is some trick. Where does one learn of such obscure Excel abilities? Thank you!!!!

 

[Rick Rothstein]

Can you post some sample data? If the lines of the address are separated by a line feed character, you can use Data|Text To Columns, select Delimited and at Step 2 check Other and with your cursor in the TextBox hold down Alt and type 010 on the numeric keypad.

An alternate to the highlighted text above is to simply press CTRL+J

 

[Andrew Poulsom]

An alternate to the highlighted text above is to simply press CTRL+J

I think you meant to highlight Alt as well. Thanks for the tip.

 

[Rick Rothstein]

I think you meant to highlight Alt as well. Thanks for the tip.

Yes, I did (actually, I thought I had selected it along with the other text, but I guess I was mistaken). The ability to edit my original message had not timed out yet, so I went back and changed it to highlight the word Alt as well, so my message now reads correctly. Thanks for noting that.

 

[jasmith4]

It's programmerese going way, way back: Alt-Enter in Excel inserts a line-feed character, whuse ASCII value is 10. If you know any character's ASCII value, you can hold down the Alt key, type the ASCII value on the numeric keypad (not the top row above QWERTYUIOP), and when you let go, that character will be inserted. Check out the Character Map (%windir%\system32\charmap.exe): use the arrow keys to navigate, and watch the bottom row.

Here are a few other characters:
Backspace=8=Ctrl-H (Backspace key)
Horizontal tab=9=Ctrl-I (Tab key)
Line Feed=10=Ctrl-J
Vertical tab=11=Ctrl-K
Form feed=12=Ctrl-L
Carriage return=13=Ctrl-M
End of file=26=Ctrl-Z
Esc=27=Ctrl-[


Generally the Enter key inserts both CR and LF on DOS systems, but just LF on *nix systems.