Extract numbers from a string of alphanumeric text

[joshdm0716]

To All Excel MVP's i need your help.
Can anyone help me with a formula to extract numbers (account number) from a string of text that contains alphanumeric. the account number starts with 249,250,251 and TLC. Length for the 249,250 and 251 is 9; And for the TLC is 11. Any help is greatly appreciated. Thank you

Example. Assuming data is located in A1.

SERVICE REF: RELATED REF: ID: INS BK:XXXXXXX SER ID:PCRM SND BK: ID: 5ERANUA00000VBHJK303 249968059 91361 ID:001235737204 : ID: TLC29676085 BVDFGR 20140703-00004704 VAL D D ORIG 250360150 GTHYKKK BNNNG 566840000078955* CORRECT BK 45366363 PCRM DIGGGI SERVICE 251253955 BKSSSDR

 

[pgc01]

Hi

This example uses a udf, that you can modify to a sub if you want.

With the text in A1,

=GetAccountNr(A1,3)

gets you: 250360150


Try:

' Gets account number, index lInd (starts at 1) from text
Function GetAccountNr(s As String, lInd As Long) As String
Dim regex As RegExp, regexMatches As MatchCollection

Set regex = New RegExp
With regex
    .Pattern = "((249|250|251)\d{6})|(TLC\d{8})"
    .Global = True
    Set regexMatches = .Execute(s)
    If regexMatches.Count >= lInd Then GetAccountNr = regexMatches(lInd - 1)
End With
End Function

Set the reference to: Microsoft VBScript Regular Expressions 5.5

 

[Redwolfx]

Will there always be one of each in the cell?

 

[joshdm0716]

Will there always be one of each in the cell?

For the most part yes. But sometimes 2 or more account number in the cell.
Should be okay to have the result in 1 cell.

 

[Redwolfx]

If there are 2 or more will they ever start with the same three digits? Like 250? And is there a maximum number of account numbers that will be in a cell?

 

[joshdm0716]

To All Excel MVP's i need your help.
Can anyone help me with a formula to extract numbers (account number) from a string of text that contains alphanumeric. the account number starts with 249,250,251 and TLC. Length for the 249,250 and 251 is 9; And for the TLC is 11. Any help is greatly appreciated. Thank you

Example. Assuming data is located in A1.

SERVICE REF: RELATED REF: ID: INS BK:XXXXXXX SER ID:PCRM SND BK: ID: 5ERANUA00000VBHJK303 249968059 91361 ID:001235737204 : ID: TLC29676085 BVDFGR 20140703-00004704 VAL D D ORIG 250360150 GTHYKKK BNNNG 566840000078955* CORRECT BK 45366363 PCRM DIGGGI SERVICE 251253955 BKSSSDR

Just to clarify..

The 1st 3 characters of the account number are always in the same position and they do not appear in the others strings that are non account number

 

[Redwolfx]

In your above example you have 4 account numbers in 1 cell, but each of them starts with a different 3 characters, If there is a cell with more than 1 account number, will the first 3 digits ever repeat?

 

[joshdm0716]

In your above example you have 4 account numbers in 1 cell, but each of them starts with a different 3 characters, If there is a cell with more than 1 account number, will the first 3 digits ever repeat?

For the most part its only 1 account number in cell. And if more than 1 account number the the first 3 digits will never repeat.

 

[István Hirsch]

I am not sure if this is what you need:

Enter (with Ctrl-Shift-Enter) this formula in B1 and drag it to the right in C1 and D1 (to extract numbers starting with 249, 250 or 251, resp.):

=IFERROR(MID($A1,MATCH(TRUE,INT(--MID($A1,ROW($1:$300),9)/10^6)=247+COLUMN(),0),9),"")

Enter (with Enter) in E1 (to extract numbers starting with „TLC”):

=IFERROR("TLC"&--MID(A1,FIND("TLC",A1)+3,8),"")

 

[NBVC]

Crossposter to multiple forums!