Extracting multiple sets of numbers from free form text string

**densley2387** · 11-01-2011, 11:40 AM

I have inherited a database which previously had a free form notes section which seems to hold all the critical data, account numbers and supply numbers. These were worked manually and the details picked out by hand, I have now changed the database completely so there will be no chance of this happening again. But I need to do some regression analysis on all the records and there is no way I am going through 300 thousand records .

The problem: I need all if any numbers from the fields, ideally with space between them so I can split them up further, I will be then be able to identify the type of number/ reference from the length.

Example of how the notes are stored:

1) a/c 123456 - Gareth deals with their unit properties - 01/08/2011 - hope to release by end of oct, 162000865535

2) 00365817 810000

3) EXISTING ACCOUNT A12345..... 20395919410 1894136301

What I have tried:=LOOKUP(99^99,--("0"&MID(AK2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},AK2&"0123456789")),ROW($1:$10000))))

Result: only get one number

=SUMPRODUCT(MID(0&AK3,LARGE(INDEX(ISNUMBER(--MID(AK3,ROW(INDIRECT("1:"&LEN($AK$2))),1))*ROW(INDIRECT("1:"&LEN($AK$2))),0),ROW(INDIRECT("1:"&LEN($ AK$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($AK$2)))/10)

Result: I get all the numbers but they are blended together.

Thank you in advance

**pike** · 11-02-2011, 06:08 AM

hi densley2387,
Welcome to the forum
maybe a user defined function will help

Option Explicit
Function All_Numbers(Rng As String)
    Dim RegEx As Object, Temp As String, OMatch As Object, OMatchCollection As Object
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = "(\d*)"
    End With
    If RegEx.test(Rng) Then
        Set OMatchCollection = RegEx.Execute(Rng)
        For Each OMatch In OMatchCollection
            Temp = Temp & " " & OMatch
        Next
    End If
    All_Numbers = Temp
    Set OMatchCollection = Nothing
    Set RegEx = Nothing
End Function

Thread: Extracting multiple sets of numbers from free form text string

Thread Tools

Display

Extracting multiple sets of numbers from free form text string

Re: Extracting multiple sets of numbers from free form text string

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions