find first non-numeric character in alphanumer text string

[stuck]

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

[HansV]

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.

[stuck]

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

[HansV]

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.