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

wanted cell would say: 26,2

Hi,

First step is to determine the common structure of all your cells ...

Carim

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

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

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