Ask a new question

Convert TEXT fractions to NUMBER decimal

Hello All,

I have a problem that I can't fix in Excel 2016.

An imported table has columns of fractions that are formatted as TEXT.

I need to convert those TEXT fractions into decimal NUMBERS.

I read a number of suggestions and tried them, but nothing works.

I even tried my own stuff, but that didn't work.

Here's what I tried that came close to working, but failed:

Copy a column of TEXT fractions, and past it into NotePad to clean-off all formatting.

Then open a new spreadsheet and format a column as FRACTION.

Then copy the NotePad column of 'fractions' and past that into the spreadsheet column that's formatted as FRACTION.

Result: All the numbers convert to fractions EXCEPT 1/2 and 3/4, which convert to DATES.

Then I select that column and format it as DECIMAL, and all the non-dates format correctly.

What is happening here?

How can I fix it?

Thanx ... Chris

You could try this formula in another column.
Say your fractions are in column A starting at A1. Put this formula in B1 and copy down.
=LEFT(A1,SEARCH("/",A1)-1)/MID(A1,SEARCH("/",A1)+1,99)
Regards

Murray
https://excel.dadsonion.com

37 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Murray,

Thanx for the formula!

It worked for fractions less than 1.

But compound fractions returned "#VALUE!".

HOW IN THE WORLD did you come up w/that formula?!?

Any ideas how to make it work with compound fractions?
Thanx! ... Chris

6 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Chris
I assume by compound fractions you mean something like 1 3/4=1.75?
Is there a space between the whole number component and the fraction part? If so try this:
=IF(ISERROR(SEARCH(" ",A1)),LEFT(A1,SEARCH("/",A1)-1)/MID(A1,SEARCH("/",A1)+1,99),LEFT(A1,SEARCH(" ",A1)-1)+MID(A1,SEARCH(" ",A1)+1,SEARCH("/",A1)-1-SEARCH(" ",A1))/MID(A1,SEARCH("/",A1)+1,99))

Here's how the original formula works:
LEFT(A1,SEARCH("/",A1)-1) looks for the position of "/" in the string, subtracts 1 from that number and takes everything from the beginning of the string up to that point.
Similarly, MID(A1,SEARCH("/",A1)+1,99) takes everything from the position of the "/"+1 up to the end of the string. Then it divides the first part by the last part.
Regards

Murray
https://excel.dadsonion.com

34 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 5, 2024 Views 24,588 Applies to: