Sum Column issues

[Guzzlr]

Hello Everybody!
got a small problem... I have a column of numbers which I need to sum. the problem is the numbers have the word "hrs" after them: 49 hrs.
So, when I sum the column, excel comes out with 0, instead of the total for the column.
How do I get excel to ignore the word "hrs", and just add the numbers?
Thanks for the help
Charles
excel 2010

 

[jdmc45]

I'm sure there's a quicker way but this will work.

First, assume that the number of hours will be no more than two digits (eg. 46 hrs).
In column A you have the hrs, in the format of 46 hrs, in column B have:

=MID(A1,1,2)

This will extract the number from the text string. Fill the formula down. It will be in text format. To convert to number format (so you can sum), in column C have:

=VALUE(B1)

Fill this formula down. Then SUM column C.

Done!

 

[jdmc45]

One other thing, to ensure it works properly, find the highest number of hours entry, say four digits (4000 hrs) then the last argument for MID should be 4, for example. Change this argument accordingly

 

[T. Valko]

Hello Everybody!
got a small problem... I have a column of numbers which I need to sum. the problem is the numbers have the word "hrs" after them: 49 hrs.
So, when I sum the column, excel comes out with 0, instead of the total for the column.
How do I get excel to ignore the word "hrs", and just add the numbers?
Thanks for the help
Charles
excel 2010

Try this...

Book1
	A
1	Time
2	34 hrs.
3	9 hrs.
4	5 hrs.
5	35 hrs.
6	8 hrs.
7	20 hrs.
8	39 hrs.
9	4 hrs.
10	32 hrs.
11	Total
12	186
Sheet1

Formula entered in A12:

=SUMPRODUCT(--(SUBSTITUTE(A2:A10,"hrs.","")))