Extracting Numbers from AlphaNumeric Strings

Topic

New Reply

Problem: In cell A1-A100, I have the following types of data: “ABC 1234-5678”, or “DEF 9012-3456 “, etc. In other words, there is text entered as the first, say, 1-25 characters of a cell, then anywhere from 1 to 50 spaces, then 4 numbers, then a hyphen, then 4 more numbers, then anywhere from 0 to 50 additional spaces. What I would like to do is extract from cells A1:A100 all of the numbers in their original format (i. e., including the hyphen, “1234-5678”, “9012-3456”) into cells B1:B100. Any ideas? Thanks in advance.

Reply | Quote

Replies

You could use this UDF:

Code:

Function GetNumbers(strInput As String) As String
    Dim RegExp As Object, match, matches
    Dim strPattern As String
    Set RegExp = CreateObject("vbscript.regexp")
    strPattern = "d{4}-d{4}"
    With RegExp
        .Global = True
        .IgnoreCase = True
        .Pattern = strPattern
         Set matches = .Execute(strInput)
        If matches.Count > 0 Then GetNumbers = matches(0)
    End With
End Function

Reply | Quote

Thanks Rory. Now, how do I get it to work?

Reply | Quote

This post has been cross posted in Another forumwhere another solution has been offered.

Reply | Quote

This post has been cross posted in Another forumwhere another solution has been offered.

Yes I did post on Eileen’s Lounge where my friend Hans resides now. Although a solution has been offered, it does not do the trick. All I would like to hear from Rory, or anyone for that matter, is how I can possibly get his UDF to work. Plain and simple! No hidden agendas here!

Reply | Quote

Yes I did post on Eileen’s Lounge where my friend Hans resides now. Although a solution has been offered, it does not do the trick. All I would like to hear from Rory, or anyone for that matter, is how I can possibly get his UDF to work. Plain and simple! No hidden agendas here!

Does it need to be a UDF?

You could use a formula such as
=right(trim(A1),9)

Reply | Quote

Thanks Gfamily. That appears to work.

Reply | Quote

FWIW, you needed to copy that code into a module, then just use it as you would any other formula:
=GetNumbers(A1)
for example.
I confess, I missed that the last characters would only be spaces not other text!

Reply | Quote