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

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

asked July 8th 19 at 12:10

2 answers

answered on

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)

=Left(

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(

answered on 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