Extracting Custom Pattern Consisting of Numbers & Text From Alphanumeric String

[Rick Rothstein]

very interesting.... thanks snb, however I'd need your function to return the following formats:
1. 3 letters LBC,ABC,FXI etc followed by 5 numerics, a back slash then 2 numerics e.g. 12540/01
2. 2000 followed by 6 numerics
3. 1800 followed by 6 numerics

If you are interested in having the output as a comma delimited string within a single cell, like 'snb' assumed you wanted, as opposed to the multi-cell output of my original UDF, you could use the following UDF (note I used the same function name, so you would have to comment out or delete my previous UDF from your module before running this one)...

Function InvoiceNumbers(S As String) As String
  Dim X As Long, OutText As String
  For X = 1 To Len(S)
    If Mid(S, X, 11) Like "[A-Za-z][A-Za-z][A-Za-z]#####/##" Then
      OutText = OutText & ", " & Mid(S, X, 11)
    ElseIf Mid(S, X, 10) Like "2000######" Or Mid(S, X, 10) Like "1800######" Then
      OutText = OutText & ", " & Mid(S, X, 10)
    End If
  Next
  If Len(S) Then InvoiceNumbers = Mid(OutText, 3)
End Function

Note... unlike my previous UDF, this one can be placed anywhere (that is, it does not have to be placed next to the cell it is processing). Also, the calling formula would be the same....

=InvoiceNumbers(A1)

[snb]

@Rick

You might have misinterpreted my suggestion: it's a UDF; so the result will be the 'filtered' value of another cell: no concatenation, no comma separation etc.

Because I have no idea what the other items look like I can only suggest something for the items provided:

Function snb_002(c00)
    snb_002 = Join(Filter(Split(Join(Filter(Split(Replace(c00, "USA", ".USA"), "."), "0"), "")), "0"))
End Function

[Rick Rothstein]

@Rick

You might have misinterpreted my suggestion: it's a UDF; so the result will be the 'filtered' value of another cell: no concatenation, no comma separation etc.

Because I have no idea what the other items look like I can only suggest something for the items provided:

Function snb_002(c00)
    snb_002 = Join(Filter(Split(Join(Filter(Split(Replace(c00, "USA", ".USA"), "."), "0"), "")), "0"))
End Function

Yes, I did misinterpret your code (sorry for not trying it first)... I saw Filter and Join and thought your code was finding the desired "number" parts (no matter what, if anything, was between them) and joining (what I was referring to when I said "concatenating") them together. I see, now, that is not what your code is doing. I do not know if this was a typo on the OP's part or not, but the first data item...

1.JET INTERNATIONALINV NO.ABC12539/01 ABC12540/01TCI12538/01

has two of his "numbers" at the end with no separation between them. Your function preserves that combined pair of "numbers" whereas both of my coded functions separate them... I am guessing adding that functionality to your nicely compact UDF would be difficult to do?

[snb]

Well it's rather complicated to write concise code for inconsistent data, but in this case:

Function snb_002(c00)
    snb_002 = Join(Filter(Split(Replace(Replace(Replace(c00, "USA", " USA"), "TCI", " TCI"), ".", " . ")), "0"))
End Function

[larkspur]

book1.jpg

Thanks again snb for your updated UDF, however this extraction is just the first part of what I'm working on. So Rick's original separated field entry works just fine

Next step is to locate those individual numbers on another workbook, then return the corresponding ID number/s and value/s, which are in different cells back to individual cells on this workbook. As I'm new to macros would either of you be able to shed some light on the process (example image attached?)