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)