Splitting an AlphaNumeric string

S

s1lverface

New member
Joined
Jun 15, 2013
Messages
4
Reaction score
0
Points
0
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..
 
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/e...rs-from-alphanumeric-strings-HA001154901.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:
Sorry, that formula needs further work...it only handles strings up to a certain length. Watch this space...
 
]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
 
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
 
...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))
 
You must log in or register to reply here.
Back
Top