how to get certain numbers

Question

Hi Guys,

I have a column in excel which is exported from a sql database.

The columns read like this:

'Payment Funds: (654321) Company charged Public - Payment of transfer.  Charged from account xyz to abc (into autopac agent).'

The reference number: 654321, account: xyz and abc; comment: (into autopac agent) - are different for all the records for that column.

I need to obtain the reference numbers only for all the records, how would I go about doing that? The preferred method is in sql, but would take spreadsheet based answer....

Thanks,

Voodoo

Answer 1

If you're using MS SQL Server then the below should work. String functions can vary between RDBMSs though. It's a good idea to specify which technology you're using in the question.

SELECT
    SUBSTRING(col, CHARINDEX('(', col) + 1, CHARINDEX(')', col) - CHARINDEX('(', col) - 1)
FROM
    Some_Table

Answer 2

Try this, its a regular expression function for Excel:

Private Function RegularExpression(SearchString As String, Pattern As String) As String 

    Dim RE As Object, REMatches As Object 

    'Create the regex object'  
    Set RE = CreateObject("vbscript.regexp") 
    With RE 
        .MultiLine = False 
        .Global = False 
        .IgnoreCase = True     
        'set the search pattern using parameter Pattern' 
        .Pattern = Pattern  
    End With 

    'Search for the pattern'  
    Set REMatches = RE.Execute(SearchString)  
    If REMatches.Count > 0 Then 
        'return the first match' 
        RegularExpression = REMatches(0)  
    Else 
        'nothing found, return empty string' 
        RegularExpression = "" 
    End If 

End Function 

you can just make it public and use it like this
=RegularExpression(A2,"[0-9]{6}") That would get out the first 6 digit sequence within cell A2
Note: this is an older function, so you might need to play around a little, but it should be fine