Extracting Custom Pattern Consisting of Numbers & Text From Alphanumeric String

[snb]

or ?

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

[Rick Rothstein]

or ?

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

Interesting... it never occurred to me that the OP might have wanted the returned value in the same cell (which I'm guessing he didn't given his response to my posting), however, that is a perfectly reasonable reading of the original posting. I don't know if you want to try to fix it or not (given the OP's response to my posting), but your output is incorrect for the 2nd sample data string....

CABLE AND WIRELESSINV. 2000680771USA

as it retains the USA which the OP showed as not desired in his example output. It is not clear if the other date format (the one that starts with three letters) could have unwanted attached letters on the end of it or not, so if you try to post a fix (for the record), you might want to assume yes and design your fix around it as well.

[larkspur]

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

[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)