Splitting cell data into columns with a return carridge

[andrewurq]

Hello all,

I am very familiar with the "text to columns" feature in excel. I want my data split into columns on what appears to be a return carriage. Delimited wont work do to inconsistent spacing. Here is an example of what the cell data looks like.

[TABLE="width: 477"]
<tbody>[TR]
[TD="class: xl63, width: 477"][TABLE="width: 477"]
<tbody>[TR]
[TD="class: xl63, width: 477"]Health Insurance Dental

Insurance Life Insurance

Insurance Unemployment

Insurance workers comp

NHPTV Fringe-Vision Service PL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




so the above is all in one cell: the spaces as I said before between these data points appears to be 2 return carriages, as far as I can tell. This spacing is consistent across all my data which is about 200 rows. By the way I didn't even know you could put carriage returns into a cell? and I need a way to split them

 

[oldbrewer]

find carriage return, replace with "999"

 

[oldbrewer]

and are you sure your carriage returns are not just ALT and return together which gives you new line in a cell - doing 2 would give you a blank space

abcde
fghijkl


mnop


these are all lines in a single cell

 

[VoG]

Data > Text to Columns, Delimited. In the Delimeter dialog click in Other, on the numeric keypad hold down ALT, type 010 then click Finish.

 

[andrewurq]

Data > Text to Columns, Delimited. In the Delimeter dialog click in Other, on the numeric keypad hold down ALT, type 010 then click Finish.

this worked perfectly thank you for the tip

 

[andrewurq]

what does 010 do anyways? like if I had 3 carriage returns would it be 0010?

 

[VoG]

It is the ASCII code for a Line Feed (CHAR(10)).