Converting 12'10" and 7' into just inches using an excel formula

[fryingpan]

I have exported data that has text values in feet and inches. The format is X'Y" unless the number is whole feet in which case it is just X'. I would like to convert to inches.

I have tired the following but it doesn't work when there are is not an inch value present. An example of this would be 7'.

=(LEFT(B1,FIND("'",B1,1)-1))*12+(MID(B1,FIND("'",B1,1)+1,LEN(B1)-FIND("'",B1,1)-1))

Thanks for the help

 

[Eric W]

Welcome to the forum.

Maybe something like this?

	A	B	C
1		7'11"	95
2		7'	84
3		11'4"	136
4		1' 3"	15
5		4'	48
6		5'0"	60

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula C1 =LEFT(B1,FIND("'",B1)-1)*12+("0"&TRIM(SUBSTITUTE(MID(B1,FIND("'",B1)+1,3),"""",""))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[fryingpan]

Eric,

Looks like you nailed it. Thanks for the help.

 

[Rick Rothstein]

Here is another formula (shorter, less function calls) that you can also use...

=12*TRIM(SUBSTITUTE(SUBSTITUTE(B1,"'"," "),"""","/12"))

 

[Eric W]

Glad we could help.

Rick, that's very clever. I often forget that Excel allows you to enter fractions.

 

[fryingpan]

Rick,

Thanks for the help as well. I agree it is a little cleaner than where I started.