Text to columns format issue with fractions

[Roseburg]

I am using text to columns to modify the following data:

[TABLE="width: 132"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1-1/8 61X145 PB[/TD]
[/TR]
[TR]
[TD]1-1/8 61X121 PB[/TD]
[/TR]
[TR]
[TD]3/4 49X97 PB[/TD]
[/TR]
[TR]
[TD]3/4 49X97 PB[/TD]
[/TR]
[TR]
[TD]3/4 49X97 PB[/TD]
[/TR]
[TR]
[TD]5/8 49X97 PB[/TD]
[/TR]
[TR]
[TD]5/8 49X97 PB[/TD]
[/TR]
[TR]
[TD]1 49X97 PB[/TD]
[/TR]
[TR]
[TD]1 49X97 PB[/TD]
[/TR]
[TR]
[TD]1/2 49X97 PB[/TD]
[/TR]
[TR]
[TD]1/2 49X97 PB

I want to see this:

[TABLE="width: 361"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD="align: right"]1.125[/TD]
[TD="align: right"]61[/TD]
[TD]X[/TD]
[TD="align: right"]145[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]1.125[/TD]
[TD="align: right"]61[/TD]
[TD]X[/TD]
[TD="align: right"]121[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]0.750[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]0.750[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]0.750[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]0.625[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]0.625[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]1.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]1.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]0.500[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]0.500[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97PB[/TD]
[TD]

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

What I get is this:

[TABLE="width: 361"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD="align: right"]39448.000[/TD]
[TD="align: right"]61[/TD]
[TD]X[/TD]
[TD="align: right"]145[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]39448.000[/TD]
[TD="align: right"]61[/TD]
[TD]X[/TD]
[TD="align: right"]121[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]43163.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]43163.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]43163.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]43228.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]43228.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]1.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]1.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]43102.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD="align: right"]43102.000[/TD]
[TD="align: right"]49[/TD]
[TD]X[/TD]
[TD="align: right"]97[/TD]
[TD]PB




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

The first column's results appear to be in a numerical date format, no matter what other format I select.

I'm using spaces as the delimiter and the result automatically formats to a numerical date format I cannot override.

Suggestions?

 

[philwojo]

On the 3rd text-to-columns screen you can change the 1st column with your fractions to be text.

You won't get the decimal equivalent, but it will keep the fraction as you see it and break out the other items.

YOu'd then have to use formulas to try and convert the fractions over to decimal. I am not sure, off the top of my head at least, how to accomplish that part though.

 

[Roseburg]

You are correct. That works. But I need to be able to use the fraction in a calculation. Doesn't work with text.

Thanks.

 

[Joe4]

The first column's results appear to be in a numerical date format, no matter what other format I select.

Those original numbers you see in there are unformatted dates. You need to understand how Excel stores dates. It stores them as number of days since 1/0/1900.
To see this, enter any date, and change the format to General and see what it shows.
It is because of the slashes in those values that Excel thinks they are dates.

You are correct. That works. But I need to be able to use the fraction in a calculation. Doesn't work with text.

Since you have values like "1-1/8", which is not a valid way to enter numbers in Excel, you cannot go straight from Text to Columns to a valid decimal date.
If you go the Text to Columns route, it will need to be two-step process, where you bring it as Text (like currently shown), then use formulas to convert it to a valid decimal number.

Otherwise, you will need to use some complex formulas, or some VBA to do it in a single step.

 

[Roseburg]

Someone else has helped with this. Adding a + to the front of each "phrase" (ie ="+"&A2), copying and pasting the result as values to another column, and THEN doing text to columns works for everything except the mixed number (treats that as a subtraction problem). But I can live with converting a couple of cells. Not really a big file.

 

[Joe4]

Someone else has helped with this.

Where? On another forum?

 

[Roseburg]

email. I realized I knew another brainiac.

 

[Roseburg]

=IF(ISERR(FIND("-",A2)),"+"&A2,"+"&REPLACE(A2,FIND("-",A2),1,"+"))

that formula eliminates the issue with the mixed number.

Thanks to everyone! Hope this is useful to someone else.