How to extract only the different part of a number/string

[feni1388]

Hello...

I have a data that contains a number or maybe a combination of a alphabet and number.
I want to extract only the different part of the number.
I can't use right function, because it might be only 1 digit or 2 digits or maybe 3 digits that are different in that group.
Has anyone any idea what function is best to use in this case?

Order code		what I want to extract is
GV343	1 group	3
GV343		3
GV343		3
GV343		3
GV343		3
GV344		4
GV344		4
3877600	1 group	0
3877600		0
3877600		0
3877600		0
3877600		0
3877610		10
3877610		10

 

[SanjayGMusafir]

I can't use right function, because it might be only 1 digit or 2 digits or maybe 3 digits that are different in that group.

If there is a way to tell Excel when to extract how many digits, we can merge RIGHT function within IFS function to get the desired results.

Trick is how to tell Excel when to extract what...

 

[Eric W]

Maybe:

Book1
	A	B	C
1	Order code		what I want to extract is
2	GV343	1 group	3
3	GV343		3
4	GV343		3
5	GV343		3
6	GV343		3
7	GV344		4
8	GV344		4
9	3877600	1 group	00
10	3877600		00
11	3877600		00
12	3877600		00
13	3877600		00
14	3877610		10
15	3877610		10
Sheet3

Cell Formulas
Range	Formula
C2:C15	C2	=LET(rng,A2:A8,len,MAX(LEN(rng)),s,SEQUENCE(,len),m,MID(INDEX(rng,1),1,s),mat,m=LEFT(rng,s),mm,MMULT(mat+0,TRANSPOSE(s)^0),mn,MIN(mm),RIGHT(rng,len-mn))
Dynamic array formulas.

It's kind of tricky to figure out what the maximum common section is. This formula requires that you enter the range for each group. That's because I don't know where each group starts and ends, and also because it's a Spill formula, so the results will spill down each group. I might be able to figure something out if you can tell me a definite way to know when each group starts/ends.

 

[feni1388]

Maybe:

Book1
	A	B	C
1	Order code		what I want to extract is
2	GV343	1 group	3
3	GV343		3
4	GV343		3
5	GV343		3
6	GV343		3
7	GV344		4
8	GV344		4
9	3877600	1 group	00
10	3877600		00
11	3877600		00
12	3877600		00
13	3877600		00
14	3877610		10
15	3877610		10
Sheet3

Cell Formulas
Range	Formula
C2:C15	C2	=LET(rng,A2:A8,len,MAX(LEN(rng)),s,SEQUENCE(,len),m,MID(INDEX(rng,1),1,s),mat,m=LEFT(rng,s),mm,MMULT(mat+0,TRANSPOSE(s)^0),mn,MIN(mm),RIGHT(rng,len-mn))
Dynamic array formulas.

It's kind of tricky to figure out what the maximum common section is. This formula requires that you enter the range for each group. That's because I don't know where each group starts and ends, and also because it's a Spill formula, so the results will spill down each group. I might be able to figure something out if you can tell me a definite way to know when each group starts/ends.

Sorry, there's supposed to be a customer code to differentiate each group.

Order code	Customer code	what I want to extract is
GV343	FPC01006	3
GV343	FPC01006	3
GV343	FPC01006	3
GV343	FPC01006	3
GV343	FPC01006	3
GV344	FPC01006	4
GV344	FPC01006	4
3877600	FPC01007	0
3877600	FPC01007	0
3877600	FPC01007	0
3877600	FPC01007	0
3877600	FPC01007	0
3877610	FPC01007	10
3877610	FPC01007	10

 

[SanjayGMusafir]

there's supposed to be a customer code to differentiate each group

@feni1388 still there is an ambiguity about how to tell excel when to choose 1, 2 or 3 digits from right

 

[Eric W]

Try this:

Book1
	A	B	C
1	Order code	Customer code	what I want to extract is
2	GV343	FPC01006	3
3	GV343	FPC01006	3
4	GV343	FPC01006	3
5	GV343	FPC01006	3
6	GV343	FPC01006	3
7	GV344	FPC01006	4
8	GV344	FPC01006	4
9	3877600	FPC01007	00
10	3877600	FPC01007	00
11	3877600	FPC01007	00
12	3877600	FPC01007	00
13	3877600	FPC01007	00
14	3877610	FPC01007	10
15	3877610	FPC01007	10
16
17
Sheet3

Cell Formulas
Range	Formula
C2:C15	C2	=LET(cc,B2,rng,FILTER($A$2:$A$100,$B$2:$B$100=cc),ix,COUNTIF(B$2:B2,cc),len,MAX(LEN(rng)),s,SEQUENCE(,len),m,MID(INDEX(rng,1),1,s),mat,m=LEFT(rng,s),mn,MIN(MMULT(mat+0,TRANSPOSE(s)^0)),RIGHT(INDEX(rng,ix),len-mn))

This seems to work ok. It's not a spill formula, you'll have to drag it down the column. It's pretty complicated, I worry a bit that it'll slow your sheet if you have a lot of rows, but try it and see.

Edit: another thought - can the customer code appear in the column for multiple groups? If so, that will be an issue.

 

[feni1388]

Try this:

Book1
	A	B	C
1	Order code	Customer code	what I want to extract is
2	GV343	FPC01006	3
3	GV343	FPC01006	3
4	GV343	FPC01006	3
5	GV343	FPC01006	3
6	GV343	FPC01006	3
7	GV344	FPC01006	4
8	GV344	FPC01006	4
9	3877600	FPC01007	00
10	3877600	FPC01007	00
11	3877600	FPC01007	00
12	3877600	FPC01007	00
13	3877600	FPC01007	00
14	3877610	FPC01007	10
15	3877610	FPC01007	10
16
17
Sheet3

Cell Formulas
Range	Formula
C2:C15	C2	=LET(cc,B2,rng,FILTER($A$2:$A$100,$B$2:$B$100=cc),ix,COUNTIF(B$2:B2,cc),len,MAX(LEN(rng)),s,SEQUENCE(,len),m,MID(INDEX(rng,1),1,s),mat,m=LEFT(rng,s),mn,MIN(MMULT(mat+0,TRANSPOSE(s)^0)),RIGHT(INDEX(rng,ix),len-mn))

This seems to work ok. It's not a spill formula, you'll have to drag it down the column. It's pretty complicated, I worry a bit that it'll slow your sheet if you have a lot of rows, but try it and see.

Edit: another thought - can the customer code appear in the column for multiple groups? If so, that will be an issue.

Thank you.
It seems to work OK.... I still need to try it with actual data though.... which it will be quite a long list.
Regarding to the customer code, it will appear in several rows randomly, but I will sort it first before applying the formula that you gave me.
So I think it's going to be OK.

 

[Eric W]

What I mean was, can you have a situation like this?

Book1
	A	B	C
1	Order code	Customer code	what I want to extract is
2	GV343	FPC01006	GV343
3	GV343	FPC01006	GV343
4	GV343	FPC01006	GV343
5	GV343	FPC01006	GV343
6	GV343	FPC01006	GV343
7	GV344	FPC01006	GV344
8	GV344	FPC01006	GV344
9	3877600	FPC01007	00
10	3877600	FPC01007	00
11	3877600	FPC01007	00
12	3877600	FPC01007	00
13	3877600	FPC01007	00
14	3877610	FPC01007	10
15	3877610	FPC01007	10
16	ABC123	FPC01006	ABC123
17	ABC122	FPC01006	ABC122
18	ABC124	FPC01006	ABC124
Sheet3

Cell Formulas
Range	Formula
C2:C18	C2	=LET(cc,B2,rng,FILTER($A$2:$A$100,$B$2:$B$100=cc),ix,COUNTIF(B$2:B2,cc),len,MAX(LEN(rng)),s,SEQUENCE(,len),m,MID(INDEX(rng,1),1,s),mat,m=LEFT(rng,s),mn,MIN(MMULT(mat+0,TRANSPOSE(s)^0)),RIGHT(INDEX(rng,ix),len-mn))

Same customer number, 2 different groups with different formats. If not, you should be ok. In fact, you shouldn't need to sort your data if you don't want to.

 

[feni1388]

What I mean was, can you have a situation like this?

Book1
	A	B	C
1	Order code	Customer code	what I want to extract is
2	GV343	FPC01006	GV343
3	GV343	FPC01006	GV343
4	GV343	FPC01006	GV343
5	GV343	FPC01006	GV343
6	GV343	FPC01006	GV343
7	GV344	FPC01006	GV344
8	GV344	FPC01006	GV344
9	3877600	FPC01007	00
10	3877600	FPC01007	00
11	3877600	FPC01007	00
12	3877600	FPC01007	00
13	3877600	FPC01007	00
14	3877610	FPC01007	10
15	3877610	FPC01007	10
16	ABC123	FPC01006	ABC123
17	ABC122	FPC01006	ABC122
18	ABC124	FPC01006	ABC124
Sheet3

Cell Formulas
Range	Formula
C2:C18	C2	=LET(cc,B2,rng,FILTER($A$2:$A$100,$B$2:$B$100=cc),ix,COUNTIF(B$2:B2,cc),len,MAX(LEN(rng)),s,SEQUENCE(,len),m,MID(INDEX(rng,1),1,s),mat,m=LEFT(rng,s),mn,MIN(MMULT(mat+0,TRANSPOSE(s)^0)),RIGHT(INDEX(rng,ix),len-mn))

Same customer number, 2 different groups with different formats. If not, you should be ok. In fact, you shouldn't need to sort your data if you don't want to.

No, there shouldn't be any situation like this. The same customer code will have order code in sequence.
Thank you for your concern.