Text to Columns Failing Me!

[red0681]

Hello Mr. Excel - I am having some problems parsing out data.
Here is an example of my data:
<TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=311><COLGROUP><COL style="WIDTH: 233pt; mso-width-source: userset; mso-width-alt: 11373" width=311><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 233pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=311>VariablesA0.9 | 4.60 | 19.77</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Vari8blesB2.04 | 6 | 30.15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>NumD.32 | 4.90 | 26</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>NamesY1 | 5.23 | 27.63</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Tit9e21.21 | 4.5 | 26.6</TD></TR></TBODY></TABLE>

I need each of these rows to appear as 4 columns, with the labels being the first column and then each set of numbers as their own cell.

I have tried text to columns, delimited and used the "|" pipe as my delimiter, but I only get back the labels and the numbers just disappear.

It's a real problem b/c there is no consistency to the length of each string or the format of the numbers. Plus some of the labels contain a numeric value. When viewed in excel there appears to be some type of "return" between the label and the numbers as well. When I click on the cell, the information appears on two "rows" in the formula bar.

Any Suggestions?
Thank you for reading my post!

 

[DonkeyOte]

First you will need to remove the Carriage returns and replace with Pipe (assuming it's acting as a delimiter)

Highlight the values and run an Edit -> Replace
Find What: hold ALT and type 0010 on numeric keypad
(on a laptop hold ALT + FN and type 0010 on numeric keypad)
Replace With: |

Once the above is done you can try Text to Columns using | delimiter
without knowing the specifics you might need to activate "Treat Consecutive Delimiters as one"

 

[red0681]

Thank you DonkeyOte! You saved the day!

Love the name.