Extracting Custom Pattern Consisting of Numbers & Text From Alphanumeric String

**larkspur** · 05-20-2013, 04:41 PM

I've number of different alphanumeric strings examples below
1.JET INTERNATIONALINV NO.ABC12539/01 ABC12540/01TCI12538/01
2.CABLE AND WIRELESSINV. 2000680771USA
3.CORPORACION 2000679533 2000679534 2000683140 2000683140 2000683140. SKY
4.SERVICIOS DIRECTOS 2000679523 2000679531 ENE 13 BBC WORLD FEB 13 BBC WORLD MAR 13 BBC WORLD SKY
I need to return the following:
1.ABC2539/01 ABC12540/01 TCI12538/01
2.2000680771
3.2000679533 2000679534 2000683140 2000683140 2000683140
4.2000679523 2000679531

Any suggestions will be much appreciated
Cheers
Larkspur

Originally Posted by Howardc

Hi Guys

Thanks for all the input, much appreciated

**Excel Fox** · 05-20-2013, 06:38 PM

The text strings you want to extract have different arrangement of numeric and text characters. The first bit uses 3 text characters 5 numerics, a back slash and 2 numerics.

The others have 10 numerics. So what's the logic?

**larkspur** · 05-20-2013, 07:05 PM

I'm trying to extract three different types of invoice numbers...
1st Appears with three letters LBC,LPA,SAA etc followed by five numbers then a back slash then two more numbers e.g. 12540/01
2nd Appears with 10 digits starting with 2000 e.g. 2000685684
3rd Appears with 10 digits starting with 1800 e.g. 1800140735

I'll probably need three separate formulas, however if I could combine the three that would be prefect...

Any ideas??

Originally Posted by Excel Fox

The text strings you want to extract have different arrangement of numeric and text characters. The first bit uses 3 text characters 5 numerics, a back slash and 2 numerics.

The others have 10 numerics. So what's the logic?

**Excel Fox** · 05-20-2013, 07:30 PM

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** · 05-20-2013, 08:08 PM

Please ignore the TCIA, typing error

Originally Posted by Excel Fox

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

**Rick Rothstein** · 05-20-2013, 08:41 PM

Originally Posted by larkspur

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

Code:

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** · 05-21-2013, 12:25 AM

Works great Thanks for the input Rothstein, much appreciated

**snb** · 05-21-2013, 02:02 AM

or ?

Code:

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

**Rick Rothstein** · 05-21-2013, 02:28 AM

Originally Posted by snb

or ?

Code:

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** · 05-21-2013, 05:24 PM

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