Text to Columns Failing Me!

red0681

New Member
Joined
Oct 7, 2010
Messages
2
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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"
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,463
Members
453,725
Latest member
cvsdatreas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top