Calculate square footage

[dekas]

Morning.
I have a column of data with dimensions entered as y' x z' and need to convert them in the next column to square footage. So far no luck with writing or recording a macro.
Hope someone has run into this before.

 

[lrobbo314]

Try this

Sub sq_ft()
Dim sqft As Range
Set sqft = Application.InputBox _
("Select First column in list to calculate square footage", "Sq. Ft Calc", Type:=8)

Do Until sqft.Offset(0, -2).Value = ""
sqft = sqft.Offset(, -2).Value * sqft.Offset(, -1).Value
Set sqft = sqft.Offset(1)
Loop
End Sub

 

[dscg]

Here's a non-macro version if the format is consistent y' x z' :

Excel Workbook
	A	B
1	5' x 6'	30 sq ft
2	14' x 5'	70 sq ft
3	2' x 100'	200 sq ft
Sheet1

 

[dekas]

Thanks to both of you.
Works great.

 

[engakadir]

Here's a non-macro version if the format is consistent y' x z' :

Sheet1

*	A	B
5' x 6'	30 sq ft
14' x 5'	70 sq ft
2' x 100'	200 sq ft

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =LEFT(A1,FIND("'",A1)-1)*SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("x ",A1)-1),"'","") & " sq ft" B2 =LEFT(A2,FIND("'",A2)-1)*SUBSTITUTE(RIGHT(A2,LEN(A2)-FIND("x ",A2)-1),"'","") & " sq ft" B3 =LEFT(A3,FIND("'",A3)-1)*SUBSTITUTE(RIGHT(A3,LEN(A3)-FIND("x ",A3)-1),"'","") & " sq ft" <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

dear bro how can i calculate (4 x 9' 0")+(4 x 8' 0") this one by your method

 

[navic]

how can i calculate (4 x 9' 0")+(4 x 8' 0")

Until you get another help, try this long formula in B2 (the source is in A2)

=IFERROR(IFERROR(LEFT(IFERROR(IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")>10),LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),SEARCH("+",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")),FIND("x",IFERROR(IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")>10),LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),SEARCH("+",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")))-1),IFERROR(IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")>10),LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),SEARCH("+",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")","")))*SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),FIND("x",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))+1,FIND("+",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),1)-FIND("x",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-2)),"'",",")*1+IFERROR(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-FIND("+",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))),FIND("x",RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-FIND("+",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))))-1),RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-FIND("+",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))))*SUBSTITUTE(SUBSTITUTE(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-FIND("*",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),"x","*",LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"(",""),")",""),"x",""))))),"'",","),"""","")*1,"")