Text to Columns

[Noddy13]

Hi,

I need to split a cell into columns where the data is imported in 9 lines with each line being its own subject such as the below. Each line should be its' own column.

Time of call: 17:17
Category: Dogs
Subject: Lost Dog
Caller: Mxxxxn Mxxxxxy
Phone: 0xxxx0xxx4
Address: 3 XXXXXXXX DR , TXXXXXXIE WA XXXX
Location:
Details: Callers M Labradoodle has been in the pound since the 8th of June and would like to get him out. Pound ID number- XXXX
Reference number: GOXXXX16

I've tried using the text to column function however there is no way to slpit by line within a cell.

Thanks

 

[shaowu459]

B1=TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",999)),(COLUMN(A1)-1)*999+1,999))
copy formula to the right cells untile the formula returns "".

 

[Noddy13]

Worked a treat.

Thanks

 

[Rick Rothstein]

I need to split a cell into columns where the data is imported in 9 lines with each line being its own subject such as the below. Each line should be its' own column.

I've tried using the text to column function however there is no way to slpit by line within a cell.

You can use Text To Columns for this... bring up the Text To Column dialog box and select Delimited on the first dialog page... on the second dialog page, click into the blank field next to the checkbox labeled "Other" and press CTRL+J. You should be able to finish up after that.

 

[jproffer]

Rick, I'm guessing Ctrl+J is some form of a carriage return character? If so, do you know what list (like key-code, uni-code, ascii, etc) that would be under and where could a person find such a list?

 

[Rick Rothstein]

Rick, I'm guessing Ctrl+J is some form of a carriage return character? If so, do you know what list (like key-code, uni-code, ascii, etc) that would be under and where could a person find such a list?

It is the old teletype keyboard character keypress for a vertical line feed. This table...

http://ascii-table.com/control-chars.php

shows you all of the old teletype Control characters. I think Excel only recognizes Ctrl+J, but only in certain places. I think Microsoft Word supports more of them.

 

[shaowu459]

Yes, it works in certain places. Format Cells---Custom---Ctrl+J also works in the textbox for Type.

 

[jproffer]

Good information, thank you sir .

I was trying to do it with T2C myself when I saw the question. I'd get to Delimiter-Other...first I tried to just hit Alt+Enter...(DINGGGGGGG), ok no can do....then I tried to type in "Chr(13)", I got as far as "C" (DINGGGGGG), well alright then...

and then I was done . Now I know (til I forget). Thanks again.