What formula to write in Excel?

Question

excel

Good day.
There is the following situation:
There is a table containing a list of order codes
The code consists of letters, numbers and the sign "_"
It always starts with a letter
It is necessary to group them into groups in the form starting from the left character to the first digit The
question is how to implement this if the digit can be in a different position of the code but not earlier than 4 characters?
Example:
Orders:
ABV0101012ABV
ABV54NERF4545
Should receive group ABV
A orders:
ABCD_D123
ABCDDE565
Should receive groups ABCD_D and ABCDDE respectively

Answer 1

You can replace all numbers with the character "*" (or any other except "_" and letters ) and take a substring up to the first found character "*":
= LEFT ( A1 ;
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
A1 ;0;"*");1;"*");2;"*");3;"*");4;"*");5 ;"*");6;"*");7;"*");8;"*");9;"*"))-1)

Answer 2

=ПСТР(A1;1;МИН(ЕСЛИОШИБКА(ПОИСК("9";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("8";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("7";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("6";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("5";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("4";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("3";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("2";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("1";A1;1);ДЛСТР(A1));ЕСЛИОШИБКА(ПОИСК("0";A1;1);ДЛСТР(A1)))-1)