Feet and Inches (Excel 2000 SR1)

[AuntLinda1]

I have pages of dimensions from an architect, e.g., 38' 6" x 6' 3" - I need a formula to tell me the square feet in this room.

Thank you in advance - again.

Linda

[LegareColeman]

The following user defined function will give you what you want:

<pre>Public Function GetArea(strSize As String) As Double
Dim strWk As String, iLen As Integer
Dim dWid As Double, dlen As Double
strWk = Trim(strSize)
iLen = InStr(strWk, "'")
dWid = Left(strWk, iLen - 1)
strWk = Trim(Right(strWk, Len(strWk) - iLen))
iLen = InStr(strWk, """")
dWid = dWid + Trim(Left(strWk, iLen - 1)) / 12
iLen = InStr(strWk, "x")
strWk = Trim(Right(strWk, Len(strWk) - iLen))
iLen = InStr(strWk, "'")
dlen = Left(strWk, iLen - 1)
strWk = Trim(Right(strWk, Len(strWk) - iLen))
iLen = InStr(strWk, """")
dlen = dlen + Trim(Left(strWk, iLen - 1)) / 12
GetArea = dWid * dlen
End Function
</pre>

This can be done using just formulas in the worksheet. However, it requires about eight formulas in eight cells because the formula gets too complex for Excel to handle in a single cell. If you are interested here are the formulas:

<pre>Cell Contents
A1 38' 6" x 6' 3"
B1 =VALUE(LEFT(A1,SEARCH("'",A1)-1))
C1 =TRIM(RIGHT(A1,LEN(A1)-SEARCH("'",A1)))
D1 =B1+VALUE(TRIM(LEFT(C1,SEARCH("""",C1)-1)))/12
E1 =TRIM(RIGHT(C1,LEN(C1)-SEARCH("x",C1)))
F1 =VALUE(LEFT(E1,SEARCH("'",E1)-1))
G1 =TRIM(RIGHT(E1,LEN(E1)-SEARCH("'",E1)))
H1 =F1+VALUE(TRIM(LEFT(G1,SEARCH("""",G1)-1)))/12
I1 =D1*H1
</pre>

[WebGenii]

Um - I totally respect your answer Legare, but wouldn't this be simpler?
=(((38*12)+6)*((6*12)+3))/12

Unless of course this is a repetitive job in which case building a function is the way to go!

[LegareColeman]

I believe that her message said that she had "Pages" of dimensions. Besides, are you looking for the simple answer or the fun answer? <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

[Leif]

.../144 methinks.

[AuntLinda1]

Thanks for the reply. I know about the /144 part - the problem is there are several occasions where, for example, the dimensions of a room might be:

(38' 6" x 6' 3") = (9' x 6' 9")

Now what do I do? I can do it, of course, but I sure would like a global something so no matter what dimension I type in Excel will figure out the square footage for me in one swift stroke.

Linda

[AuntLinda1]

This is terrific - I just hope I'm correct in assuming I write this as a macro?

[LegareColeman]

The top one is a macro. You put it into a VBA module, then if the dimensions are in A1, you would enter something like this in the cell where you want the area:

<pre>=GetArea(A1)
</pre>


The bottom half are spreadsheet formulas. You just copy the formulas and paste them into the cells indicated. Then if the dimensions are in Column A, just copy the formulas down the columns.

[AuntLinda1]

You are a prince among men. Thank you, thank you.

I obviously need to learn VBA and macro use - any recommendations?

[WebGenii]

I can't compete with you in the FUN department <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> - so I was trying for <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15> simple! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[LegareColeman]

You are in one of the best places to learn. Just ask about anything you don't understand.

[AuntLinda1]

Okay - here goes.

I probably wasn't very clear when I posted my first question. This is what I should have told everyone:

The information I get from the architects looks like this:

(38' 6" x 6' 3") + (9' x 6")

How do I enter this information and where do I put what parts of it?

I've made a spreadsheet - it's attached. What do you think?

Linda

[LegareColeman]

Well, that is a bit different! See if the attached workbook will do what you want.

[AuntLinda1]

Can't wait to see what you've sent me but I get an "unable to launch application. Appliation not found." message when I try to open it. Saved it to my downloads file - tried to open it from there. got the "cannot find the file . . ." message. HELP!

Linda

[LegareColeman]

I don't know what might be happening to you. I can click on the file and it opens fine. I used Excel 97 to create the file, but that should not be a problem if you have Excel 2000.

The first message that you gave indicates that your computer can not find the Excel program if that is what is linked to the .xls file extension. The second message says that it can find the .xls file, and this usually indicated that there is a problem with the definition of the .xls file type link.

I would suggest two things:

1- Rename the file so that it is less than 8 characters and contains no blanks.

2- Try starting Excel and then use the File/Open menu to open the file.

One more question. Can you click on the file that you uploaded and open it?