What formula to write in Excel?

Good time of day.
Is the following scenario:
There is a table containing a list of order codes
Code consists of letters, digits and underscore "_"
Always starts with a letter
They need to be grouped into sections starting with the leftmost character on the first digit
Question - how to implement it, if the figure may be in a different position of the code but not earlier than 4 characters?
Example:
Orders:
АБВ0101012АБВ
АБВ54НЕРФ4545
Need to get the ABV group
And orders:
АБВГ_Д123
АБВГДЕ565
Needs to group ABVGD and BUGGE respectively
July 8th 19 at 12:10
2 answers
July 8th 19 at 12:12
Solution
You can replace all the numbers with the character "*"( or any other except "_" and letters ) and take the substring until the first occurrence of symbol "*":
=Left(A1;FIND("*";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1;0;"*");1;"*");2;"*");3;"*");4;"*");5;"*");6;"*");7;"*");8;"*");9;"*"))-1)
July 8th 19 at 12:14
=PSTR(A1;1;MIN(ALIOSHENKA(POISK("9";A1;1);delstr(A1));ELISHEBA(POISK("8";A1;1);delstr(A1));ELISHEBA(POISK("7";A1;1);delstr(A1));ELISHEBA(POISK("6";A1;1);delstr(A1));ELISHEBA(POISK("5";A1;1);delstr(A1));ELISHEBA(POISK("4";A1;1);delstr(A1));ELISHEBA(POISK("3";A1;1);delstr(A1));ELISHEBA(POISK("2";A1;1);delstr(A1));ELISHEBA(POISK("1";A1;1);delstr(A1));ELISHEBA(POISK("0";A1;1);delstr(A1)))-1)

Find more questions by tags Excel