UDF for extracting only numeric values.

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

Faraz A. Qureshi

Any UDF code to have entries like following:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

Thanx in advance!
 
The function will return 0 if there is no digits in the string. I can modify
this to return a Null string very easily.

Function GetNum(Data As String)
GetNum = ""
For CharPos = 1 To Len(Data)
Char = Mid(Data, CharPos, 1)
If IsNumeric(Char) Then
GetNum = GetNum & Char
End If

Next CharPos
GetNum = Val(GetNum)
End Function
 
Hi,

How about a formula with your string in A1. This is an array formula, see
below. Once array entered then drag down as required.
With respect to
Lars-Ã…ke Aspelin who first posted it.

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($A$1:$A$300)-1)*ISNUMBER(-MID("01"&A1,ROW($A$1:$A$300),1)),ROW($A$1:$A$300))+1,1),10^(300-ROW($A$1:$A$300))),2,300)


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
Any UDF code to have entries like following:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

Thanx in advance!
Click to expand...

=====================
Function Nums(s As String)as String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D+"
Nums = re.Replace(s, "")
End Function
========================

Note that by returning the result as String, leading zero's can be retained. If
this is not wanted or desireable, then return result as Long or as Double.
--ron
 
Back
Top