Ignoring text after number?

[afc171]

Hi all,
How can I add up totals (range F8:F17) but ignore any text after the number?

23.5

4MG

5 ml

so total would be 32.5 ?

Thank you

 

[Dave Patton]

Are the numbers with text always single digits?
Can you post a more complete example?
With 2013, possibly array enter.

T202501a.xlsm
	F	G
8	23.5
9	4MG
10	5 ml
11	32.5	32.5
4g

Cell Formulas
Range	Formula
F11	F11	=SUM(IF(ISNUMBER(F8:F10),F8:F10,--LEFT(F8:F10)))
G11	G11	=SUMPRODUCT(IF(ISNUMBER(F8:F10),F8:F10,--LEFT(F8:F10)))

 

[Scott Huish]

If you're up to VBA, a very simple function could help with this:

Function ConvertValue(r As Range) As Double
Dim c As Range
For Each c In r
    ConvertValue = ConvertValue + Val(c)
Next
End Function

Then in your spreadsheet:
=ConvertValue(F8:F10)

 

[afc171]

If you're up to VBA, a very simple function could help with this:

Function ConvertValue(r As Range) As Double
Dim c As Range
For Each c In r
    ConvertValue = ConvertValue + Val(c)
Next
End Function

Then in your spreadsheet:
=ConvertValue(F8:F10)

Thanks for you help guys, this seems to work pretty well. Cheers.