Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Extracting Custom Pattern Consisting of Numbers & Text From Alphanumeric String

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0

    Extracting Custom Pattern Consisting of Numbers & Text From Alphanumeric String

    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

    Quote Originally Posted by Howardc View Post
    Hi Guys

    Thanks for all the input, much appreciated

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    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??

    Quote Originally Posted by Excel Fox View Post
    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?

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Please ignore the TCIA, typing error

    Quote Originally Posted by Excel Fox View Post
    what about the PM you sent me? The last one here has 4 characters (TCIA)

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by larkspur View Post
    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.
    Last edited by Rick Rothstein; 05-21-2013 at 01:24 AM.

  7. #7
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    Works great Thanks for the input Rothstein, much appreciated
    Last edited by Admin; 05-22-2013 at 03:30 PM. Reason: removed quote

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    or ?

    Code:
    Function snb_002(c00)
        snb_002 = Join(Filter(Split(Join(Filter(Split(c00, "."), "0"), "")), "0"))
    End Function
    Last edited by Rick Rothstein; 05-21-2013 at 02:27 AM.

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    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.

  10. #10
    Junior Member
    Join Date
    May 2013
    Posts
    6
    Rep Power
    0
    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

Similar Threads

  1. Extract Number From Alphanumeric Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 10
    Last Post: 09-11-2013, 10:14 PM
  2. Replies: 6
    Last Post: 06-01-2013, 03:24 PM
  3. Extracting Numeric Values From Alphanumeric Text
    By Safal Shrestha in forum Excel Help
    Replies: 3
    Last Post: 03-21-2013, 12:04 PM
  4. Find a text substring that matches a given "pattern"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 02-10-2013, 06:19 AM
  5. Extract numbers from alphanumeric values
    By tushar.tarafdar in forum Excel Help
    Replies: 3
    Last Post: 09-20-2012, 10:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •