Wow!!

So many things to consider other than the sample OP posted.


Thanks Ron

On Tue, 23 Dec 2008 16:12:18 -0500, Ron Rosenfeld
wrote:

On Tue, 23 Dec 2008 12:07:01 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

=RemAlpha(cellref)*1 the *1 produces a numeric value


Gord Dibben MS Excel MVP

A few comments:

Your formula: RemAlpha(cell_ref)*1 will return a #VALUE! error if there were
no digits in cell_ref.

So if you wanted to return a numeric value, with a #VALUE! error if there are
no digits, you could modify your UDF:

Option Explicit
Function RemAlpha(str As String) As Variant
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = CDbl(re.Replace(str, ""))
End Function


and then use the simpler formula =RemAlpha(cell_ref).

---------------------------------
Also, your routine will remove decimals. In other words, pail=19.3kg would
return 193 and not 19.3. If decimal values are a possibility, there are
several other approaches.

If the only "dot" could be in the number, then you could change pattern to
"[^\d.]"

Of course, this would fail with "pail=19.3kg."

So what you could use is a regex that would extract a floating point number.

Perhaps:

Dim re as object, mc as object
Set re = createobject("vbscript.regexp")
re.Pattern = "\d*\.?\d+"
If re.test(str) = True then
Set mc = re.Execute(str)
end if
RemAlpha = mc(0).Value
--ron