How do I sum up only the numbers in cells containing text and numb

R

Ron Rosenfeld

cell A1: text 12 kg
cell A2: blah 14,2 xx

wanted cell would say: 26,2
Click to expand...

You extract the numeric values and sum them.

But in order to extract the numbers, you need to know the possible variations.

In your example, the numeric values are always the next-to-last word. Will
that always be the case?

Will it always be the case that the numeric values in which you are interested
will be the ONLY numbers in the string, or could you have something like:
The 1st 6 items weigh 12,3 kg.

If the latter, will the numeric values of interest always be the last numeric
value?

Oh, and does the function have to be internationally aware? In other words,
will you always be using the comma as a "decimal" or could some users be using
the "dot" with the comma being used as a digit separator?


--ron
 
G

Gord Dibben

One more method...use a macro to strip out the text.

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points
'change [0-9.] to [0-9,] if using comma for a decimal point
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben MS Excel MVP
 
Top