Retrieve number from string

[Jan Kronsell]

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

 

[Joel]

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

 

[Ron Rosenfeld]

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

 

[Jan Kronsell]

Thank you to you both.

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