can area calculations be simplified in excel?

[musataq]

I have a problem ..i need to calculate the square FEET area of some shapes..now the dimensions I have for them are mostly like 1’7’’ and 45”…now what I want is that instead of converting 1’7” to 1.58333’ in the calculator every now and then…I want that I just enter 1’7” into excel and it should automatically take it in feet and perform the rest of the arithmetic calculations..what do I need to do for this?

thanks

[ChemistB]

If 1'7" is in A1
In B1
=TRIM(MID(A1,1,SEARCH("'",A1)-1))+TRIM(MID(A1,SEARCH("'",A1)+1,LEN(A1)-SEARCH("'",A1)-1))/12

Does that work for you?

[Moo the Dog]

Don't know if there is an easier way to do this, but this will convert a string in your format (1'7") to a decimal {assume your string is in A1}:

Please Login or Register  to view this content.

Note, there isn't any error checking in this formula, so if one of your measurements is less than 1', it would need to be entered as 0'7", etc.

- Moo

[MrShorty]

A different thought (maybe because I am just not familiar or proficient with Excel's text manipulation functions). Rather than enter the text string 1'7" and use text formulas to extract the 1 and 7, how would you feel about entering it as a fraction? If you enter the number 1 7/12 (7 inches is 7/12 of a foot), Excel will receive that as a your fractional foot entry. For an entry like 45", you would enter 0 45/12 or (once the cell is formatted) as 45/12. An approach like this will avoid the difficulties that come from manipulating text strings, but it does mean you would have to deal with the difficulties related to entering fractions into Excel.