Strip/Grep numbers and not letters

[shdawson]

Hi,


I have a column that is a listing of scheduled work. Unit-of-measure is hours. Examples of this include:
0.25h
3.00h
3h
2,450h


What I need to do is total these numbers, based upon other colums that show department and what not. I can SUMPRODUCT that part, no problem.

It seems like dropping the "h" and converting to a number is the way to go. It would be oh-so-nice to do this in a formula.

So, to that end....is there a formula combo that can do this, please? Or, is it easier to break into 2 actions. 1 formula to strip the "h", and then another to do the sum-ing part?


Thanks,
SHD

 

[Jonmo1]

You might try

=LEFT(A1,LEN(A1)-1)+0


That could probably be included in your sumproduct...

 

[VoG]

I would just strip out the h

Excel Workbook
	A	B
1	0.25h	0.25
2	3.00h	3
3	3h	3
4	2,450h	2450
Sheet3

 

[shdawson]

OK, that formula will work for now. I will mess wtih it a bit and see about integrating into 1 formula or going with 2 actions of stripping the "h" then summing.

Thank you very much.


Kindly,
SHD

 

[shdawson]

BTW, why does this formula need the +0 at the tail end?


Thanks,
S

 

[Jonmo1]

It doesn't actually need it..

The Left function returns a TEXT string.
If you try to reference it with another formula, it won't be considered a real number.
Adding the +0 converts the text string into a real number

 

[shdawson]

Wow, now that is trivia. Converting to a number with +0, way cool!


Thanks,
S

 

[Scott Huish]

In one formula:

=SUMPRODUCT(--(SUBSTITUTE(A1:A4,"h","")))

Excel Workbook
	A	B	C
1	0.25h		2456.25
2	3.00h
3	3h
4	2,450h
Sheet1

 

[Rick Rothstein]

Wow, now that is trivia. Converting to a number with +0, way cool!

The "general rule" is if you involve a text string that looks like a number in a mathematical operation, Excel will convert the text string to its numerical value in order to be able to complete the mathematical operation. Adding 0 to your text string involves it in a mathematical operation and the addition of 0 is used so the overall calculation will not change the text string's numerical value once the conversion has occurred. Two other "standard" ways of doing this would be...

=1*LEFT(A1,LEN(A1)-1)

=--LEFT(A1,LEN(A1)-1)

where the double minus sign is the equivalent of multiplying by minus one twice (-1 * -1 = +1).