How to convert feet & inches to a decimal?

assuming the values for feet and inches are in column A and you want the corresponding decimal feet to be in column B, you could do the following:

select cell B2, then type, or copy and paste from here, the formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(34), ""), CHAR(8220), ""), CHAR(8221), ""), CHAR(8217), "_"), CHAR(8216), "_"), CHAR(39), "_")

shorthand for this is:

B2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(34), ""), CHAR(8220), ""), CHAR(8221), ""), CHAR(8217), "_"), CHAR(8216), "_"), CHAR(39), "_")

C2=SEARCH("_", B2, 1)

D2=LEFT(A2, C2−1)

E2=SUBSTITUTE(SUBSTITUTE(B2,D2&"_","",occurrence),D2&"_","",occurrence)

F2=D2+E2÷12

to fill down, select cells B2 thru F2, copy

select cells B2 thru the end of column F, paste

this assumes that the format of the entries in column A is:

F'I"

where F = number of feet

and

I = number of inches

the reason for the long formulas is there are variations of single and double quotes that can cause trouble.

the double quote comes in three forms:

1) simple ascii

2) fancy unicode open double quote

3) fancy unicode close double quote

same thing for the single quote mark

The only thing I can think of is that the occurrence should be omitted... try this instead:

=SUBSTITUTE(SUBSTITUTE(B2,D2&"_",""),D2&"_","")

to fill down, select cells B2 thru F2, copy

select cells B2 thru the end of column F, paste

can you post the formula you are using and a screenshot of what's happening?

I think the syntax error is generated because your screenshot shows you have an extra = at the beginning of the formula in E2. In the formula editor remove that extra = and all should be well.

SG

good catch SG. I was tired in my last reply