help converting Fractions with/without whole number

[jafvortex93]

Hello im having an issue with a formula i found in another thread that i tried applying to my circumstance. Im trying to convert fractions into decimals. The fractions might be whole numbers with a fraction or just a fraction by itself.

ex: Column D Row 37 (could be row 23-46) has a fraction "3/32", in cell J37 i put the following formula: =0+TRIM(SUBSTITUTE(A1,CHAR(160)," "))

The result it gives me is "11749", but when the same cell (D37) has "1 3/32" it gives me "1.09375" (desired result).

Changing Cell D37 to fraction format gives me the result i want in J37 but changes the value in D37.

[quekbc]

For Excel reasons, 3/32 is recognised as a date, that is March 1932, which is 11749.

Try this instead: For fractions recorded as text (D1 and D2 of your file) =0+TRIM(SUBSTITUTE(IF(ISERROR(FIND(" ",D1)),"0 ","") & D1,CHAR(160)," "))

[AlKey]

Try this formula in B1 and copy down

=IF(ISERROR(FIND(" ",A1)),--("0 "&A1),--TRIM(SUBSTITUTE(A1,CHAR(160)," ")))

if your range is different change A1 to to whatever your range is

Data Range

	A	B
1	3/32	0.09375
2	1 3/32	1.09375
3	1 8/16	1.5
4	2 8/32	2.25

[jafvortex93]

@quekbc and @alkey Thanks guys . Both formulas worked perfectly. Can either of you break down the formula for me? i would like to better understand what exactly it is saying.

Edit:

Apparently excel can convert fractions for you by changing the format to "number" and adjusting the decimal places as needed. and then just changing the format back to fraction (it even simplifies the fraction).

[AlKey]

With IF(ISERROR(FIND(" ",A1)) formula will check if there is a space and if not it will return an error. So, in this case it will invoke first formula --("0 "&A1), otherwise it will use --TRIM(SUBSTITUTE(A1,CHAR(160)," ")). Double-unary "--" will convert text to a real number.