How do I sum up only the numbers in cells containing text and numb
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
On Fri, 6 Oct 2006 04:27:02 -0700, LfmC wrote:
cell A1: text 12 kg
cell A2: blah 14,2 xx
wanted cell would say: 26,2
|