Text to columns format issue with fractions

Roseburg

New Member
Joined
Feb 13, 2018
Messages
5
I am using text to columns to modify the following data:

1-1/8 61X145 PB
1-1/8 61X121 PB
3/4 49X97 PB
3/4 49X97 PB
3/4 49X97 PB
5/8 49X97 PB
5/8 49X97 PB
1 49X97 PB
1 49X97 PB
1/2 49X97 PB
1/2 49X97 PB

I want to see this:

1.12561X145PB
1.12561X121PB
0.75049X97PB
0.75049X97PB
0.75049X97PB
0.62549X97PB
0.62549X97PB
1.00049X97PB
1.00049X97PB
0.50049X97PB
0.50049X97PB


<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

What I get is this:

39448.00061X145PB
39448.00061X121PB
43163.00049X97PB
43163.00049X97PB
43163.00049X97PB
43228.00049X97PB
43228.00049X97PB
1.00049X97PB
1.00049X97PB
43102.00049X97PB
43102.00049X97PB





<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.
 
Upvote 0
You are correct. That works. But I need to be able to use the fraction in a calculation. Doesn't work with text.

Thanks.
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
=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.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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