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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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

New Member
Joined
Feb 13, 2018
Messages
5
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,457
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 13, 2018
Messages
5
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. :)
 

Roseburg

New Member
Joined
Feb 13, 2018
Messages
5
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,567
Messages
5,469,453
Members
406,652
Latest member
LJA

This Week's Hot Topics

Top