I've done this before I'm sure but the life of me I can't repeat the trick.
I want to capture the leading (numeric) characters from a text string, e.g. given 02169Rf*, I want just the '02169', and yes the leading zero is important. I can't just use =left(A1,5) because in some cases it will be left(A1,4).
Ken
find first non-numeric character in alphanumer text string
-
- Panoramic Lounger
- Posts: 8178
- Joined: 25 Jan 2010, 09:09
- Location: retirement
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: find first non-numeric character in alphanumer text stri
As an array formula (confirm with Ctrl+Shift+Enter):
=LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW($1:$10),1)),0)-1)
The 10 in the formula is the maximum length of the string; increase if necessary.
=LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW($1:$10),1)),0)-1)
The 10 in the formula is the maximum length of the string; increase if necessary.
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8178
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: find first non-numeric character in alphanumer text stri
Magic!
But why does it need to refer to a number of rows for the max string length? Why not just use '10'? (apart form the fact it doesn't work when you do use '10')
Ken
But why does it need to refer to a number of rows for the max string length? Why not just use '10'? (apart form the fact it doesn't work when you do use '10')
Ken
-
- Administrator
- Posts: 78541
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: find first non-numeric character in alphanumer text stri
ROW($1:$10) is a convenient way to generate the list of numbers {1,2,3,...,10}.
MID(A1,ROW($1:$10),1) splits the value of cell A1 into separate characters; in your example 02169Rf* it results in {"0","2","1","6","9","R","f","*","",""}.
1* this array results in number values for the numeric strings, and in error values for non-numeric strings: {0,2,1,6,9,error,error,error,error,error}.
ISERROR converts this to TRUE/FALSE values: {FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE}
The MATCH function returns the index of the first TRUE value, 6 in this example because the first TRUE is in the 6th position.
LEFT(A1, <match>-1) returns the characters from A1 before that, i.e. the numeric ones.
MID(A1,ROW($1:$10),1) splits the value of cell A1 into separate characters; in your example 02169Rf* it results in {"0","2","1","6","9","R","f","*","",""}.
1* this array results in number values for the numeric strings, and in error values for non-numeric strings: {0,2,1,6,9,error,error,error,error,error}.
ISERROR converts this to TRUE/FALSE values: {FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE}
The MATCH function returns the index of the first TRUE value, 6 in this example because the first TRUE is in the 6th position.
LEFT(A1, <match>-1) returns the characters from A1 before that, i.e. the numeric ones.
Best wishes,
Hans
Hans