Results 1 to 6 of 6

Thread: Splitting an AlphaNumeric string

  1. #1

    Splitting an AlphaNumeric string



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey ! - Can anyone help. I have 100,000+rows of data. Column A has Alphanumeric such as PTS23145SUMMERT0789. I need to strip this to show me everything after the first batch of numbers. so in this example I want to return SUMMER0789. All of my Alphanumerics are different and random, ....... Hmmmm. ~For some reason my keyboard 'return key' doesn't seem to work when typing in this forum? Sorry..

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Assuming PTS23145SUMMERT0789 is in A1, ARRAY ENTER this in A2 (i.e. enter it with Ctrl + Shift + Enter):
    =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))
    Put this in A3 (no need to array enter):
    =MID(A1,FIND(B1,A1)+LEN(B1),LEN(A1))

    Formula from http://office.microsoft.com/en-us/ex...001154901.aspx

    If you want a one-cell formula, then you can use this:
    =MID(A1,FIND(MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))),A1)+LEN(MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))),LEN(A1))
    Last edited by JeffreyWeir; 2013-06-25 at 01:00 AM.

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Sorry, that formula needs further work...it only handles strings up to a certain length. Watch this space...

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    ]Okay, this should do it:

    Code:
    =MID(MID(A5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A5),LEN(A5))),LEN(A5)),MATCH("|",IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),LEN(A1))),LEN(A1))),"|"),0)-1,LEN(MID(A5,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A5),LEN(A5))),LEN(A5))))
    Arrray entered with Ctrl + Shift + Enter

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    And then there's this gem:
    Code:
    =MID(A1,MATCH(1,ISNUMBER(1*MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))*NOT(ISNUMBER(1*MID(A1,ROW(INDEX(A:A,2):INDEX(A:A,LEN(A1)+1)),1))),0)+1,LEN(A1))
    Array entered

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    ...or this beauty:
    Code:
    =MID(A1,1+MATCH(1,(CODE(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1))<58)*(CODE(MID(A1&"a",ROW(INDEX(A:A,2):INDEX(A:A,LEN(A1)+1)),1))>57),0),LEN(A1))

Posting Permissions

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