# Thread: Splitting an AlphaNumeric string

1. ## Splitting an AlphaNumeric string

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

3. Sorry, that formula needs further work...it only handles strings up to a certain length. Watch this space...

4. ]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. 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. ...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
•