Retrieve number from string
 

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

Retrieve number from string
 

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

Retrieve number from string
 

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

Retrieve number from string
 

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