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)) ) ) )

 

[Joe4]

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

That is a very important detail you failed to mention until now!
While there are many similarities between the two, Excel and Google Sheets are different programs and do NOT work exactly the same.

In the future, if you post Google Sheets questions, please be sure to:
1. Mention in the initial post or title that it is a Google Sheets question.
2. Post it in the appropriate forum (NOT the "Excel Questions" forum).
Note the description on the "General Discussion & Other Applications" forum:

I have moved this thread and updated your thread title.

 

[ddub25]

Ok, thanks @Joe4, sorry

 

[FormR]

I am working in Google Sheets

Hi, for google sheets, you can try like this.

=IF(C4="","",IF(ISNUMBER(C4),C4,LET(S,SPLIT(SUBSTITUTE(C4,"/"," ")," "),IF(FIND(" ",C4&" ")<FIND("/",C4),INDEX(S,1)+(INDEX(S,2)/INDEX(S,3)),(INDEX(S,1)/INDEX(S,2))))))

 

[ddub25]

Hello again @FormR, thanks for your help. That almost works for every scenario. It works...

When,
C4 is formatted as 0" mm", And
- C4 contains A number (550, 420, 12 etc)

When,
C4 is formatted as Plain Text, And
- C4 contains A whole number and a fraction (21 5/8 ", 3 1/4 " etc)
- C4 contains A fraction on its own (1/2 ", 2/5 " etc)

But not When,
C4 is formatted as Plain Text, And
- C4 contains A whole number on its own (12, 22 etc)

Can you offer an update that would cater for when C4 contains A whole number on its own?

 

[FormR]

Not tested, but you can try this small addition.

=IF(C4="","",IF(ISNUMBER(C4+0),C4,LET(S,SPLIT(SUBSTITUTE(C4,"/"," ")," "),IF(FIND(" ",C4&" ")<FIND("/",C4),INDEX(S,1)+(INDEX(S,2)/INDEX(S,3)),(INDEX(S,1)/INDEX(S,2))))))

 

[ddub25]

Thanks for the suggestion @FormR. Adding the +0 doesn't seem to work as it returns #VALUE! when C4 = 15 " for example.

 

[FormR]

when C4 = 15 "

I'm pretty sure that's the first time you've mentioned this possibility

Anyway, you can try:

=IF(C4="","",IF(ISNUMBER(C4),C4,LET(S,SPLIT(SUBSTITUTE(C4,"/"," ")," "),IF(ISNUMBER(FIND("/",C4)),IF(FIND(" ",C4&" ")<FIND("/",C4),INDEX(S,1)+(INDEX(S,2)/INDEX(S,3)),(INDEX(S,1)/INDEX(S,2))),index(s,1)+0))))

 

[FormR]

A little shorter..

=IF(C4="","",LET(S,SPLIT(SUBSTITUTE(C4,"/"," ")," "),F,FIND("/",C4),IF(ISNUMBER(F),IF(FIND(" ",C4&" ")<F,INDEX(S,1)+(INDEX(S,2)/INDEX(S,3)),(INDEX(S,1)/INDEX(S,2))),INDEX(S,1)+0)))

 

[ddub25]

Thanks very very much @FormR, that works great. It's very much appreciated - it has been giving me a headache for a while.

I'm pretty sure that's the first time you've mentioned this possibility

Maybe it was, sorry.

Thanks again, Dan