how do you convert room dimensions 13' 8" X 9' 2" to
square feet. Can you put 13' 8" in the same cell ?
AFAIK, Excel does not recognize that form. But you might double-check the
"special" and "custom" formats of your version.
Two alternatives come to mind.
The first is a clever (too clever?) use of DOLLARDE and DOLLARFR functions,
which are part of the ATP for XL2003; presumably, they were mainstreamed in
XL2007 and later.
This requires that you enter your feet as integers and inches as decimal
fractions divided by 100. For example, 13.08 and 9.02. You can use the
Custom format 0"' ".00\" to get almost the appearance that you use above.
(But note that we cannot get rid of the decimal point.)
With such values in A1 and A2, the square measurement can be calculated as
follows:
=DOLLARFR(DOLLARDE(A1,12)*DOLLARDE(A2,12),12) formatted with the same Custom
format.
The second approach is to enter feet-inches amounts exact as you do above,
namely: 13' 8" and 9' 2". Note that Excel will treat them as text. You
must then use text functions like MID, LEFT, RIGHT and FIND to manipulate
the text. I suggest that you use helper cells in order to reduce
replication; but you can choose to combine the individual formulas into a
single one-liner.
If A7 contains feet-inches text, then use B7 to convert to a decimal number
as follows:
=LEFT(A7,FIND("'",A7)-1)+LEFT(RIGHT(A7,3),2)/12
or
=LEFT(A7,FIND("'",A7)-1)+MID(LEFT(A7,LEN(A7)-1),FIND("'",A7)+1,99)/12
The first simpler formula is limited to integer inches separated from feet
by at least one space. The second formula is more robust; it permits
non-integer inches (e.g. 8.125), and it does not require the one-space
separator.
If B7 and B8 contain your two feet-inches amounts converted to decimal
numbers, the square feet is simply =B7*B8.
I don't know if it makes sense to present that in the feet-inches form. But
it you want that, you could do the following:
=INT(B7*B8)&"' "&ROUND(12*MOD(B7*B8,1),0)&""""