Extracting variable amounts of characters from highly variable strings into two columns

[bearminmaxing]

I have item IDs that I need to sort, but excel sorts them incorrectly, so I'm trying to extract parts of the IDs to sort them as needed. I've included an example image.

The different item IDs have prefixes that don't change as frequently and often group specifications of items together, then suffixes that change with each individual item. I'd like to separate the prefixes into one column, and the suffixes into another column (ex. ABC12DEF34; prefix ABC12DEF, suffix 34). Because of how many variables I understand if I can't use the same formula across all item IDs, but if possible I'd like to use two formulas only: one formula for the column extracting the prefix and one formula for the column extracting the suffix.

 

[hagia_sofia]

Hello, it is not certain whether the following covers all the possibilities so let us see:

=DROP(REDUCE("",A2:A8,LAMBDA(x,y,VSTACK(x,
LET(
array,y,
a,CHAR(SEQUENCE(26,,65)),
b,MAX(IFERROR(SEARCH(a,array),0)),
c,--(MID(array,b+1,LEN(array)-b)),
d,MID(array,1,b),
IFERROR(HSTACK(--(TEXTAFTER(array,"-")),TEXTBEFORE(array,"-")),HSTACK(c,d)))))),1)

 

[hagia_sofia]

So, yesterday I have not considered the possibility that there might be multiple instances of the same letter within one cell which could cause error. The adjusted formula should address it:

=DROP(REDUCE("",A1:A2,LAMBDA(x,y,VSTACK(x,
LET(
array,y,
seq,SEQUENCE(LEN(array)),
split,MID(array,seq,1),
a,CHAR(SEQUENCE(26,,65)),
b,MAX(XLOOKUP(a,split,seq,0,,-1)),
c,--(MID(array,b+1,LEN(array)-b)),
d,MID(array,1,b),
IFERROR(HSTACK(--(TEXTAFTER(array,"-")),TEXTBEFORE(array,"-")),HSTACK(c,d)))))),1)

 

[Peter_SSs]

Welcome to the MrExcel board!

I'd like to use two formulas only: one formula for the column extracting the prefix and one formula for the column extracting the suffix.

Is this the sort of thing you are after?

24 09 05.xlsm
	A	B	C
1		Suffix	Prefix
2	123A0000-1	1	123A0000
3	123A0000-123	123	123A0000
4	456B1111-22	22	456B1111
5	ABC12DEF1	1	ABC12DEF
6	ABC12DEF10	10	ABC12DEF
7	ABC12DEFG123	123	ABC12DEFG
8	ABC34GHJ12345	12345	ABC34GHJ
Split

Cell Formulas
Range	Formula
B2:B8	B2	=--TAKE(TEXTSPLIT(SUBSTITUTE(A2,"-","Z"),CHAR(SEQUENCE(,26,65))),,-1)
C2:C8	C2	=SUBSTITUTE(LEFT(A2,LEN(A2)-LEN(B2)),"-","")

 

[bearminmaxing]

Welcome to the MrExcel board!


Is this the sort of thing you are after?

24 09 05.xlsm
	A	B	C
1		Suffix	Prefix
2	123A0000-1	1	123A0000
3	123A0000-123	123	123A0000
4	456B1111-22	22	456B1111
5	ABC12DEF1	1	ABC12DEF
6	ABC12DEF10	10	ABC12DEF
7	ABC12DEFG123	123	ABC12DEFG
8	ABC34GHJ12345	12345	ABC34GHJ
Split

Cell Formulas
Range	Formula
B2:B8	B2	=--TAKE(TEXTSPLIT(SUBSTITUTE(A2,"-","Z"),CHAR(SEQUENCE(,26,65))),,-1)
C2:C8	C2	=SUBSTITUTE(LEFT(A2,LEN(A2)-LEN(B2)),"-","")

Wow, that's perfect, thank you!

 

[bearminmaxing]

So, yesterday I have not considered the possibility that there might be multiple instances of the same letter within one cell which could cause error. The adjusted formula should address it:

=DROP(REDUCE("",A1:A2,LAMBDA(x,y,VSTACK(x,
LET(
array,y,
seq,SEQUENCE(LEN(array)),
split,MID(array,seq,1),
a,CHAR(SEQUENCE(26,,65)),
b,MAX(XLOOKUP(a,split,seq,0,,-1)),
c,--(MID(array,b+1,LEN(array)-b)),
d,MID(array,1,b),
IFERROR(HSTACK(--(TEXTAFTER(array,"-")),TEXTBEFORE(array,"-")),HSTACK(c,d)))))),1)

Thanks, I appreciate the help! I went ahead and used Peter_SSs's formulas, I unfortunately found it rather difficult to implement this formula, but thank you for taking the time to come up with this solution!

 

[felixstraube]

Just for fun another solution:

HighLowDataPoints.xlsx
	A	B	C
1	Item Number	Sort#	SortAZ
2	132A0000-1	1	132A0000
3	132A0000-123	123	132A0000
4	456B1111-22	22	456B1111
5	ABC12DEF1	1	ABC12DEF
6	ABC12DEF10	10	ABC12DEF
7	ABC12DEFG123	123	ABC12DEFG
8	ABC34GHJ12345	12345	ABC34GHJ
9	ABC34GH*12346	12346	ABC34GH*
10	ABC34GH\666	666	ABC34GH\
11	ABC34GH\		ABC34GH\
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=LET(d, A2, l, LEN(d), s, SEQUENCE(,l), i, MAX(s*ISERROR(VALUE(MID(d, s, 1)))), IFERROR(VALUE(MID(d, i+1, l-i)), "") )
C2:C11	C2	=LET(d, A2, l, LEN(d), s, SEQUENCE(,l), i, MAX(s*ISERROR(VALUE(MID(d, s, 1)))), SUBSTITUTE(MID(d, 1, i), "-","") )

With this formula you can have any non numeric characters and it will take the last number sequence and extract it.

 

[Peter_SSs]

Wow, that's perfect, thank you!

You’re welcome. Thanks for the follow-up.