Converting special characters

C

calimari

Is it possible to get the sum of cells using numbers entered in feet? I
other words...I need to calculate a sum using numbers with the ' &
symbol next to the number indicating feet & inches.

Is there a special way to do this, or do I need to convert the data t
whole numbers?

Thank you in advance. :
 
S

swatsp0p

Assuming you don't have mixed feet and inches within the same cell, yo
could use the following formula to 'read' the text entry of feet as
number and sum them together using an array formula:

=SUM(VALUE(LEFT(A1:A100,1)))

confirmed with Control+Shift+Enter, not just Enter.

the values of 1', 3' and 5' will return 9

If your cells contain mixed feet and inches, you can convert to decima
feet (e.g. 1' 3" = 1.25 feet) and sum the results:
(note: this assumes your data is entered with a space between the ' an
the next character, e.g. 1'_3")

=LEFT(A1,FIND("'",A1)-1)+(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(
",A1)-1)/12)

HT
 
G

Gary's Student

If you have a column of data in the form:

1&2

meaning one foot, two inches, then use Data > Text to Columns.. feature to
separate the data into a column for feet and a column for inches.

Once you have summed th columns, you can either convert the inches to feet
or feet to inches and add them up.
 
S

swatsp0p

my original formula for finding feet only did not take into account fo
numbers larger than 9. Use this instead:

=SUM(VALUE(LEFT(F15:F17,FIND("'",F15:F17)-1)))

again, this is an array formula and need to be entered usin
CTRL+SHIFT+ENTER.

Good Luck

Bruc
 
Top