Extract Numerics only
On Tue, 8 Jan 2008 11:20:02 -0800, katdot
wrote:
This is the only one that works for me, but I lose the last number. My values
look more like this:
1+0756
1+0789AH
1+0478BK
I know this is an old topic, but hopefully someone can help.
This UDF will return all the numbers in the string. If there are NO numbers,
it returns #VALUE! That can be modified, if you wish.
================================
Option Explicit
Function Nums(str As String) As Double
Dim re As Object, mc As Object
Const sPat As String = "\D"
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
Nums = re.Replace(str, "")
End Function
===================================
To enter this <alt-F11 opens the VBEditor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code above
into the window that opens.
Then use the formula =Nums(cell_ref) on your worksheet.
--ron
|