How to preserve text formatting

[total21]

I have a colum of data, which as an example, looks something like this:

[TABLE="width: 527"]
<tbody>[TR]
[TD="class: xl63, width: 527"]0.33|third|1/3

When I do Data -> Text to columns it will work correctly, but this will turn into 0.33 third Jan-3

I dont need anything like Jan-3 and all cells need to be kept as they are. Is there any way to prevent that. I've tried formatting all cells as General or Text, but it does not work. 1/3 still gets changed to Jan-3.

Thanks. [/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

When you get to Step 3 of 3 in the Text To Column dialog box, select the third column (the one with 1/3 in it) and then click the Text option button, then click the Finish button.

 

[total21]

When you get to Step 3 of 3 in the Text To Column dialog box, select the third column (the one with 1/3 in it) and then click the Text option button, then click the Finish button.

Yes this works. The inner window of step 3 of 3 is actually clickable, and it can be adjusted there.

Actually, it is only showing 3 columns there (and I can change them all to text), and some of the rows have like 20 columns. Is there any way to adjust that (I can do something like Ctr+A for selecting all, it looks like).

Thanks.

 

[Rick Rothstein]

Actually, it is only showing 3 columns there (and I can change them all to text), and some of the rows have like 20 columns. Is there any way to adjust that (I can do something like Ctr+A for selecting all, it looks like).

With the first column highlighted (the default), if you Shift-Click the last column, all the columns will become selected.

 

[total21]

With the first column highlighted (the default), if you Shift-Click the last column, all the columns will become selected.

I kind of cant get to the last one, it seems like. I would need to scroll through 15,000+ rows, and it does not look like I would be able to do it, I think.

 

[Rick Rothstein]

I kind of cant get to the last one, it seems like. I would need to scroll through 15,000+ rows, and it does not look like I would be able to do it, I think.

Last column not last row (the rows are selected by default).

 

[Jonmo1]

I think what OP is saying is you have mixed data down the column.
Some rows have only 2 or 3 delimiters
Some have many.
In the TTC window, you only see a preview of a few rows.
If that preview window doesn't contain a row with many delimters, then you only see a few of the resulting columns.
If you scroll down to a point where a row in the preview window contains many delimiters, THEN you can see many columns.

I don't think there's any way around that.
You'll just have to scroll the window until you hit a row with the max delimiters.

 

[total21]

Last column not last row (the rows are selected by default).

I know, I am talking about columns. Just like the post #7, it is hard to do what is needed on 15,000 rows. I would need to somehow scroll to that row 8,500 (or whatever else), which would have these 20 columns, and I would need to mark all 20. It does not seem to be a doable thing.

 

[Jonmo1]

You could add a helper column to count how many delimiters are in each cell.
=LEN(A1)-LEN(SUBSTITUTE(A1,"yourdelimiter",""))

Then sort the data in Descending order by the column with the formula.
Then the max cell with the most delimiters will be on the top of the column.

 

[total21]

You could add a helper column to count how many delimiters are in each cell.
=LEN(A1)-LEN(SUBSTITUTE(A1,"yourdelimiter",""))

Then sort the data in Descending order by the column with the formula.
Then the max cell with the most delimiters will be on the top of the column.

This worked. Thanks!