NON-VBA: FORMULA NEEDED - Looping through string and stop on non-numeric

Hello,

May I ask how to "loop" through a string using formula (NOT VBA) and on the 1st instance that the criteria is met (in this case a non-numeric), give me the output.

e.g1 the string is: 05abcd123456789xyz000

Since I know that the 1st 2 strings will always be numbers, follow by 4 letters, I want to check from the 7th string onwards, "loop" through the remaining string and once it is not a number, stop and return from the 1st string. In the above example, the ANSWER will be 05abcd123456789

Originally Posted by XOR LX

Hi,

=MID(A1,1,6+MATCH(FALSE,INDEX(ISNUMBER(0+MID(A1,7+ROW($1:$999),1)),,),0))

Regards

Thanks XOR LX! Reps up to you!

I still don't understand the formula how it works :'(

By the way, I tried to use the formula, and when I tried to vlookup it, it gives me an error even though the RANGE_LOOKUP is set to TRUE

mid function gives 05abcd123456789
vlookup table 05abcd123456789abc

Any idea how to make a partially string to match the table and give the "match"

Originally Posted by XOR LX

Sorry - I'd need a bit more information. We don't really want to get into the realm of "fuzzy" matching here.

We could simpy concatenate with "abc" prior to doing the lookup, of course, but then that won't help if you have other examples which don't end in "abc".

Perhaps post a workbook with a few examples.

Regards

I've attached an example here...thanks for your help!

Originally Posted by XOR LX

Bit messy, and perhaps not guaranteed to always work. Try, in C2:

=LOOKUP(2^15,SEARCH(D2,$G$2:$G$5),$H$2:$H$5)

though I don't know where you get your expected result for 05FIAJ100051144 from. There's nothing in your table remotely like it.

Regards

No...that's perfect!!! Thanks once again for your help!