Convert inch fractions to decimal, improve formula

I have length in inches written in a the common way: 1/4" or 3" or 2 1/4".
I wish to convert these numbers to decimal.
I did some lazy googling, both on the www and on this site specifically but nothing life changing popped up.

I then solved the task myself, the solution looking like this:

Formula:

Please Login or Register  to view this content.

Not the worst formula I have ever produced but certainly not the best either.

Can this formula be made in a smarter, shorter, better way?

And how is that CONVERT thing supposed to work? It gives all kinds of numbers.

I don't have a better way to convert from fractional format in text to decimal numeric format without writing it in VBA.

But you are using CONVERT on strings; it is intended to be used with numeric values. I don't know the complete specification for CONVERT (Microsoft is really bad at giving rigorous specifications) but from your results it appears that:

CONVERT can successfully convert a text string of a mixed fraction to a numeric value (e.g., 1 1/4). All your integers and mixed fractions (rows 8-14) are converted correctly

CONVERT cannot successfully convert a string of a proper fraction. However, instead of giving a #VALUE! error, it converts the fraction to a date (e.g., interprets 1/4 as April 4). This gives hilarious results (rows 2-7).

I found this one a long time ago, I can't remember where but I saved it for converting from fractions to decimals...
=IFERROR(SUBSTITUTE("0 "&A2,"""","")+0,SUBSTITUTE(A2,"""","")+0)

Wow! That was brutally simple!

Wow! That was brutally simple!

Isn’t it? I wish I could remember where I found it, I give the person who wrote it attribution.

AND thank you for the rep!

Fun fact; I made a column where I removed the " sign and then tried to apply the core of Sam's formula:

Formula:

Please Login or Register  to view this content.

but that FAILS!
Presumable because the fraction is in a cell where Excels perverted "turn-anything-into-a-date" -function strikes its claws into it.
In Sam's formula, the " -less values are just an intermediate result and out of reach of the freaky date thing.

I used to be an administrator for a product where I would pull data and one of the variables was in ranges and one of the ranges was 10-13 and every time I'd pull that data it would be put out as a CSV file and excel would always convert it to Oct 13 and whatever year we were in. Finally I went into the programming for the application and put ' in front of the range - just that one. That fixed the issue. I could not seem to get around the "freaky date thing" as you put it.

I haven't seen this suggested, but I find this is the simplest way to deal with fractional entries in Excel.

1) KEY STEP. Preformat the data entry cells as fraction (I used # #/#). This will cause Excel to interpret hand entered data as fractional numbers. In your file, I selected I2:I20 and preformatted as fraction. I note that this doesn't work as well for data imported from a text file. You haven't stated where the data are coming from, so I assumed it was hand entered.
2) With the cells preformatted, I can just enter the fractions as they are in column A -- without the trailing " character.
3) I can now replicate column B in column J with a simple =I2 formatted as general.
4) I can replicate column C in column K with a simple =J2*25.4

If the data are being hand entered, then I would find this straightforward by taking the extra effort before data entry to make sure Excel will enter the numbers correctly at the data entry point.

Does that help at all?

Originally Posted by Sam Capricci

I found this one a long time ago, I can't remember where but I saved it for converting from fractions to decimals...
=IFERROR(SUBSTITUTE("0 "&A2,"""","")+0,SUBSTITUTE(A2,"""","")+0)

This taught me that Excel can convert the string "0 1/8" into 0.125 with forced coercion, but not "1/8". This is consistent with my post on how CONVERT works.

Originally Posted by MrShorty

I haven't seen this suggested, but I find this is the simplest way to deal with fractional entries in Excel.

...

I kind of knew about this, I have played around with it before. I even made a custom list that produces the most common pipe sizes that I could then use for both filling and sorting. A surprisingly nice feature.

Originally Posted by 6StringJazzer

This taught me that Excel can convert the string "0 1/8" into 0.125 with forced coercion, but not "1/8". This is consistent with my post on how CONVERT works.

Yeah. I love Excel and all the freedom it gives me but in some cases Excel fails hard for no apparent reason (point in case) which is really annoying.