Hi

I'm gtrying to retrieve a number from a string. The string is a cell value,
that can be

AAAAAA 12 AAA
AA12
12AA
1A2AAA12
AAAA 12 AAAA

or any other combination of letters and numbers. I need at function, that
rerieves only the numbers. The results of above should be

12
12
12
1212
12

Regards
Jan

Function GetNum(Target As Range)

InputData = Target.Value
GetNum = ""
Do While InputData < ""
If Left(InputData, 1) = "0" And _
Left(InputData, 1) <= "9" Then

GetNum = GetNum & Left(InputData, 1)
End If

InputData = Mid(InputData, 2)

Loop

End Function


"Jan Kronsell" wrote:

Hi

I'm gtrying to retrieve a number from a string. The string is a cell value,
that can be

AAAAAA 12 AAA
AA12
12AA
1A2AAA12
AAAA 12 AAAA

or any other combination of letters and numbers. I need at function, that
rerieves only the numbers. The results of above should be

12
12
12
1212
12

Regards
Jan

On Mon, 21 Jul 2008 12:26:25 +0200, "Jan Kronsell"
wrote:

Hi

I'm gtrying to retrieve a number from a string. The string is a cell value,
that can be

AAAAAA 12 AAA
AA12
12AA
1A2AAA12
AAAA 12 AAAA

or any other combination of letters and numbers. I need at function, that
rerieves only the numbers. The results of above should be

12
12
12
1212
12

Regards
Jan



Enter the UDF below.
<alt-F11 opens the VB Editor
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

You can then use the formula: =ReSub(cell_ref, Pattern) to "remove" what you
don't want (this is more efficient than returning what you do.

=resub(A1,"\D+") will remove everything that is NOT a digit, returning only
digits.

The function returns the value as Text. If you want it returned as a Numeric
value, then use:


=--ReSub(A1,"\D+")

or

=VALUE(ReSub(A1,"\D+"))



=====================================
Option Explicit
Function ReSub(str As String, sPat As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
ReSub = re.Replace(str, "")
End Function
=====================================
--ron

Thank you to you both.

Jan

Ron Rosenfeld wrote:
On Mon, 21 Jul 2008 12:26:25 +0200, "Jan Kronsell"
wrote:

Hi

I'm gtrying to retrieve a number from a string. The string is a cell
value, that can be

AAAAAA 12 AAA
AA12
12AA
1A2AAA12
AAAA 12 AAAA

or any other combination of letters and numbers. I need at function,
that rerieves only the numbers. The results of above should be

12
12
12
1212
12

Regards
Jan



Enter the UDF below.
<alt-F11 opens the VB Editor
Ensure your project is highlighted in the project explorer window,
then Insert/Module and paste the code below into the window that
opens.

You can then use the formula: =ReSub(cell_ref, Pattern) to "remove"
what you don't want (this is more efficient than returning what you
do.

=resub(A1,"\D+") will remove everything that is NOT a digit,
returning only digits.

The function returns the value as Text. If you want it returned as a
Numeric value, then use:


=--ReSub(A1,"\D+")

or

=VALUE(ReSub(A1,"\D+"))



=====================================
Option Explicit
Function ReSub(str As String, sPat As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
ReSub = re.Replace(str, "")
End Function
=====================================
--ron