Extracting Custom Pattern Consisting of Numbers & Text From Alphanumeric String

[Excel Fox]

what about the PM you sent me? The last one here has 4 characters (TCIA)

Details appear in a number of different ways e.g.
1.MEDIA VISION DE PA 2000679547,2000679548,2000685031,2000685031,200068 5031 SKY
2.SVERIGESTELEVISIOLBC64745/01AGRREF810130423100000835
3.AVJET INTERNATIONA INV NO.TCIA12539/01 TCA12540/01TCI12538/01
I'd like to be able to return the following..
1. 2000679547 2000679548 2000685031 2000685031 2000685031
2. LBC64745/01
3. TCIA12539/01 TCA12540/01 TCI12538/01

I'd just love one excel formula to return the following results if possible

Can anyone help?
Cheers
Larkspur

[larkspur]

Please ignore the TCIA, typing error

what about the PM you sent me? The last one here has 4 characters (TCIA)

[Rick Rothstein]

Please ignore the TCIA, typing error

I'm sure Excel Fox won't mind if I jump in here. Based on your previous descriptions of your invoice numbers and what your data column values look like couple with how you want the data split out, I have a UDF (user defined function) for you to consider (I could not think of anyway to do it with normal Excel functions)...

Function InvoiceNumbers(Cell As Range) As String
  ' This function assumes output will start one cell to the right of the specified Cell argument
  Dim X As Long, Index As Long, S As String, OutText As String
  S = Cell.Text
  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
  Index = Application.Caller.Column - Cell.Column - 1
  If Len(S) Then InvoiceNumbers = Split(Trim(OutText) & Space(Len(S)))(Index)
End Function

Assuming your data starts in cell A1, put this formula...

=InvoiceNumber($A1)

in cell B1 (note the $ sign to freeze the column letter) and copy it across for as many columns as you think you might ever need your data split out into and then copy all those cells down to the bottom of your data.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. See the example formula and instructions above.

[larkspur]

Works great Thanks for the input Rothstein, much appreciated