GOOGLE SHEETS: Converting fractional text to a number

[ddub25]

The mini sheet example contains text of 1/2 " in C4 and a formula in D4 that is aimed at converting that text into it's correct number value. The result should be 0.5 and not 45323.00, can anyone help with correcting the formula?

Book1
	C	D
4	1/2 "	45323.00
Sheet1

Cell Formulas
Range	Formula
D4	D4	=IF(ISBLANK(C4), "", IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(C4, " mm", ""), """", ""))), VALUE(SUBSTITUTE(SUBSTITUTE(C4, " mm", ""), """", "")), IF(ISERROR(FIND(" ", C4)), VALUE(LEFT(C4, FIND("/", C4)-1)) / VALUE(MID(C4, FIND("/", C4)+1, LEN(C4) - FIND("/", C4))), VALUE(LEFT(C4, FIND(" ", C4)-1)) + VALUE(MID(C4, FIND(" ", C4)+1, FIND("/", C4) - FIND(" ", C4)-1)) / VALUE(MID(C4, FIND("/", C4)+1, FIND("""", C4)-FIND("/", C4)-1)) ) ) )

 

[arthurbr]

There should be a shorter way

=LEFT(SUBSTITUTE(A1,CHAR(34),""),SEARCH("/",SUBSTITUTE(A1,CHAR(34),""))-1)/MID(SUBSTITUTE(A1,CHAR(34),""),SEARCH("/",SUBSTITUTE(A1,CHAR(34),""))+1,99)

 

[Cubist]

Perhaps

Book1 (version 2).xlsb
	C	D
4	1/2 "	0.5
Sheet6

Cell Formulas
Range	Formula
D4	D4	=LEFT(C4,FIND("/",C4)-1)/MID(SUBSTITUTE(C4,"""",""),FIND("/",C4)+1,99)

 

[Georgiboy]

Here's one that will work with various measurements:

Logistics Revised.xlsx
	C	D
4	1/4 "	0.25
5	1/2 mm	0.5
6	1/8 g	0.125
7	1/3 '	0.333333
8	1/5	0.2
Sheet2

Cell Formulas
Range	Formula
D4:D8	D4	=LEFT(C4,FIND("/",C4)-1)/LEFT(RIGHT(C4,LEN(C4)-FIND("/",C4))&" ",FIND(" ",C4&" ")-2)

 

[Alex Blakenburg]

Using @Georgiboy's example another option is below:
on the original example it can be simplified to:

=--("0 " &SUBSTITUTE(C4,CHAR(34),""))

Book5
	C	D
4	1/4 "	0.25
5	1/2 mm	0.5
6	1/8 g	0.125
7	1/3 '	0.333333
8	1/5	0.2
Sheet4

Cell Formulas
Range	Formula
D4:D8	D4	=--("0 "&LEFT(C4,IFERROR(FIND(" ",C4)-1,LEN(C4))))

For more information see: How to enter and display fractions in Excel | TechRepublic

 

[ddub25]

Thanks. All of your suggestions solve the problem if C4 = 1/2 “ or other fractions. (Although this suggestions didn't work: =--("0 "&LEFT(C4,IFERROR(FIND(" ",C4)-1,LEN(C4)))))

The reason why my original formula was a bit more complex was because C4’s value will change and may contain a fraction with a whole number like 1 1/2 “, for example, and may also contain just a number with no symbol like, 550 for example. When C4 contains fractions, or whole numbers with fractions, it is formatted as text, but when it contains a number like 550, it is formatted as a number.

My original formula works for all those scenarios, apart from if C4 = 1/2 “ or other fractions. Is it possible to incorporate your formula into the original formula?

 

[FormR]

Hi, here's another option that tries to cater for the new scenarios.

Book2
	C	D
4	1/4 "	0.25
5	1/2 mm	0.5
6	1/8 g	0.125
7	1/3 '	0.333333
8	1/3	0.333333
9	560	560
10	1 1/5	1.2
11	1 3/8 "	1.375
12	10 7/12 g	10.58333
Sheet1

Cell Formulas
Range	Formula
D4:D12	D4	=IF(C4="","",IF(ISNUMBER(C4),C4,0+(IF(FIND(" ",C4&" ")<FIND("/",C4),"","0 ")&(LEFT(C4,FIND(" ",C4&" ",FIND("/",C4))-1)))))

 

[Alex Blakenburg]

(Although this suggestions didn't work: =--("0 "&LEFT(C4,IFERROR(FIND(" ",C4)-1,LEN(C4)))))

Did it only not work because you seem to have picked up an additional ")" ?
As you can see in the XL2BB above and the one below it definitely works on the example format originally provided.
Having a whole number already added does change the requirement though.

Book5
	C	D
4	1/2 "	0.5
Sheet6

Cell Formulas
Range	Formula
D4	D4	=--("0 "&LEFT(C4,IFERROR(FIND(" ",C4)-1,LEN(C4))))

 

[Alex Blakenburg]

I crossed paths with @FormR's suggestion and his does seem to handle all your scenarios.

 

[ddub25]

I am working in Google Sheets and thought that the syntax between the 2 programs was the same when it came to formulas in the formula bar. I am guessing that is why some suggestions are not working.

@Alex Blakenburg, I entered your formula as: =--("0 "&LEFT(C4,IFERROR(FIND(" ",C4)-1,LEN(C4)))) correctly on this occasion, but it still returned ‘#VALUE!.’ in Excel or Google Sheets when entering a whole number and fraction (1 1/2 “), or a number (560). It works with a fraction (1/2 “) formatted as text.

@FormR, your formula ‘=IF(C4="","",IF(ISNUMBER(C4),C4,0+(IF(FIND(" ",C4&" ")<FIND("/",C4),"","0 ")&(LEFT(C4,FIND(" ",C4&" ",FIND("/",C4))-1)))))’ works for all scenarios in Excel, but not for any fractions, or whole numbers & fractions in Google Sheets. I assume the Plain Text format in Google Sheets upsets things.