Split cell at transition from number to any other character

[jlove]

Say we have "100112model2", I want to split out the 100112 piece. The kicker for me is its not left(a1, 6) because the count of numbers before the first non-numeric character is variable. Could also "4432/mebb4" - Here again I need the 4432 piece.
Thanks

 

[Nine Zero]

Hello try this out.....

=LEFT(A1,MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)-1)

*This is an array formula which is used by doing CTRL + SHIFT + ENTER while in the formula bar and then drag down your list*

 

[jlove]

That is very cool. Thank you!

 

[Rick Rothstein]

Assuming your number is never longer than 8 digits (formula can be fixed if it is, just tell us what the longest possible number is), here is another formula you can try (one less function call and normally entered)...

=LEFT(A1,SUM(ISNUMBER(-MID(A1,{1,2,3,4,5,6,7,8},1))*NOT(ISNUMBER(-MID(A1,{2,3,4,5,6,7,8,9},1)))*{1,2,3,4,5,6,7,8}))

 

[Peter_SSs]

Again assuming the leading number is no more than 8 digits, here's one with a few less function calls again

=LEFT(A1,AGGREGATE(15,6,{1,2,3,4,5,6,7,8,9}/ISERROR(MID(A1,{1,2,3,4,5,6,7,8,9},1)+0),1)-1)

 

[Peter_SSs]

.. or considerably shorter:

=LEFT(A1,MATCH(TRUE,ISERROR(MID(A1,{1,2,3,4,5,6,7,8,9},1)+0),0)-1)