UDF for extracting only numeric values.
 

Any UDF code to have entries like following:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

Thanx in advance!
--
Best Regards,

Faraz

UDF for extracting only numeric values.
 

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

"Faraz A. Qureshi" wrote:

Any UDF code to have entries like following:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

Thanx in advance!
--
Best Regards,

Faraz

UDF for extracting only numeric values.
 

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
"Faraz A. Qureshi" wrote:

Any UDF code to have entries like following:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

Thanx in advance!
--
Best Regards,

Faraz

UDF for extracting only numeric values.
 

On Fri, 2 Oct 2009 02:54:01 -0700, Faraz A. Qureshi
wrote:

Any UDF code to have entries like following:

1234Text
12Text34
Text1234

be converted to:

1234
1234
1234

Thanx in advance!

=====================
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