Convert a dimesion to decimal?

[jrisebo]

How would you convert 5'-6" to 5.5? I have this, but always get 5

=IFERROR(--LEFT(G46,FIND("'",G46)-1),0)+IFERROR(--SUBSTITUTE(IFERROR(MID(G46,FIND("'",G46)+1,2),G46),CHAR(34),""),0)/12

 

[Joe4]

Try this:

=TEXTBEFORE(G46,"'")+IFERROR(SUBSTITUTE(TEXTAFTER(G46,"-"),CHAR(34),"")/12,0)

I added an error command to handle a situation where maybe you have no inches, like:
5'

 

[dreid1011]

Edit: Better use Joe's suggestion.

Maybe this:

Book1
	A	B
1	5'-6"	5.5
2	4'-3"	4.25
3	10'-11"	10.91667
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=TEXTBEFORE(A1,"'")+TEXTAFTER(SUBSTITUTE(A1,"""",""),"-")/12

 

[jrisebo]

Try this:

=TEXTBEFORE(G46,"'")+IFERROR(SUBSTITUTE(TEXTAFTER(G46,"-"),CHAR(34),"")/12,0)

I added an error command to handle a situation where maybe you have no inches, like:
5'

I get a #name? error.

 

[dreid1011]

I get a #name? error.

Are you using Office 365 like your profile shows?

 

[jrisebo]

Are you using Office 365 like your profile shows?

Im on Microsoft 365 apps for enterprise. I dont know what version of excel.
Edit - 2202 Build 14931.20858

 

[dreid1011]

Im on Microsoft 365 apps for enterprise. I dont know what version of excel.
Edit - 2202 Build 14931.20858

Could be build 2208 and newer. That's about all I can find by googling. But it seems more folks earlier on build 2202 had similar issues and others on 2210 did not.

The link is to a Microsoft support forum post showing the build 2208 I am referring to.

Redirecting

answers.microsoft.com

Edit: further confirmation:

 

[jrisebo]

Could be build 2208 and newer. That's about all I can find by googling. But it seems more folks earlier on build 2202 had similar issues and others on 2210 did not.

Redirecting

answers.microsoft.com

my online version of excel has it (if I log into 365). But what they installed on my system does not. Ugh
But, I know how to do it now, I can wait a few days or whatever for them to update it.

 

[Joe4]

Im on Microsoft 365 apps for enterprise. I dont know what version of excel.
Edit - 2202 Build 14931.20858

It looks like that build is a year old, from December 2022, and is on the Semi Annual channel.
So it would seem that you have some updates to apply. If you do, I think you should get access to all the latest and greatest functions.

 

[jrisebo]

It looks like that build is a year old, from December 2022, and is on the Semi Annual channel.
So it would seem that you have some updates to apply. If you do, I think you should get access to all the latest and greatest functions.

I guess I will ask my IT department