Extracting numbers from alphanumeric string

[Ales]

Hi,

I found this formula. How can it be mended to extract the numbers from
alphanumeric data into a new column? The number also includes other
characters e.g. "/" and "-" or a letter which would need to be included on
the extraction and could be at the end or beginning of the string (basically
street name and house number)
e.g.
55 Grove Hill
Rűpniecîbas iela 2789
Senatorska 18A
Ul. Brzezińska 50/15

Many thanks
Ales

 

[Ron Rosenfeld]

Hi,

I found this formula. How can it be mended to extract the numbers from
alphanumeric data into a new column? The number also includes other
characters e.g. "/" and "-" or a letter which would need to be included on
the extraction and could be at the end or beginning of the string (basically
street name and house number)
e.g.
55 Grove Hill
R?pniecîbas iela 2789
Senatorska 18A
Ul. Brzezi?ska 50/15

Many thanks
Ales

This User Defined Function will return the first "word" in the string that
contains at least one digit.

In your examples above, it will return

55 Grove Hill --> 55
R?pniecîbas iela 2789 --> 2789
Senatorska 18A --> 18A
Ul. Brzezi?ska 50/15 --> 50/15




To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=AddrNum(A1)

in some cell.

===========================================
Option Explicit
Function AddrNum(s As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b\S*\d\S*\b"
If re.test(s) = True Then
Set mc = re.Execute(s)
AddrNum = mc(0)
End If
End Function
============================================
--ron