Help to get characters from different cells based on same character

[gargilang]

Hello, I would like to know if it's possible to have some formula to get the specific characters from two or three different cells based on same character in it. I am using this formula at the moment

=LEFT(A2,FIND("_",A2)-1)

to get the characters I want. for example below table:

1_aaaa_x	1
2_aaaa_y	2
3_bbbb_x	3

Now what I want is something like this:

1_aaaa_x	1, 2
2_aaaa_y	2, 1
3_bbbb_x	3, 4, 5
4_bbbb_y	4, 3, 5
5_bbbb_z	5, 3, 4

And now if it's not too much to ask already, is it also possible to have the formula to add "&" to the cell, for example:

1_aaaa_x	1 & 2
2_aaaa_y	2 & 1
3_bbbb_x	3, 4 & 5
4_bbbb_y	4, 3 & 5
5_bbbb_z	5, 3 & 4

if not than it's totally okay as this is only for aesthetic.

I hope it makes sense. Kindly let me know if it's possible. Thank you for your help in advance.

 

[Fluff]

My corporate Excel doesn't recognize the LET Function

Do you have the Filter function?

 

[gargilang]

Do you have the Filter function?

unfortunately no Filter as well.

 

[Fluff]

In that case what version of xl are you using at work?

 

[gargilang]

In that case what version of xl are you using at work?

Version 1908 (Build 11929.20648)
Office 365 MSO (16.0.11929.20436)
Semi-annual Channel

 

[Fluff]

Ouch, that is a very old version of xl, it must be from 2019 or early 2020.
It will be simpler to use your two helper columns like

Fluff.xlsm
	A	B	C	D
1
2	1	aaaa	1_aaaa_x	1 & 2
3	2	aaaa	2_aaaa_y	2 & 1
4	3	bbbb	3_bbbb_x	3, 4 & 5
5	4	bbbb	4_bbbb_y	4, 3 & 5
6	5	bbbb	5_bbbb_z	5, 3 & 4
Data

Cell Formulas
Range	Formula
D2:D6	D2	=SUBSTITUTE(TEXTJOIN(", ",,A2,IF(($B$2:$B$10=B2)*($A$2:$A$10<>A2),$A$2:$A$10,"")),","," &",COUNTIFS($B$2:$B$10,B2)-1)

 

[gargilang]

Ouch, that is a very old version of xl, it must be from 2019 or early 2020.
It will be simpler to use your two helper columns like

Fluff.xlsm
	A	B	C	D
1
2	1	aaaa	1_aaaa_x	1 & 2
3	2	aaaa	2_aaaa_y	2 & 1
4	3	bbbb	3_bbbb_x	3, 4 & 5
5	4	bbbb	4_bbbb_y	4, 3 & 5
6	5	bbbb	5_bbbb_z	5, 3 & 4
Data

Cell Formulas
Range	Formula
D2:D6	D2	=SUBSTITUTE(TEXTJOIN(", ",,A2,IF(($B$2:$B$10=B2)*($A$2:$A$10<>A2),$A$2:$A$10,"")),","," &",COUNTIFS($B$2:$B$10,B2)-1)

Thank you, thank you, and once again thank you for keep answering. But unfortunately even with the Substitute, it still doesn't work.

 

[Fluff]

Forgot, you will need to confirm it with Ctrl Shift Enter, rather than just Enter.

 

[gargilang]

Forgot, you will need to confirm it with Ctrl Shift Enter, rather than just Enter.

I love you Sir! Wherever you are!!!
Thank you so much for the help!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[gargilang]

Ouch, that is a very old version of xl, it must be from 2019 or early 2020.
It will be simpler to use your two helper columns like

Fluff.xlsm
	A	B	C	D
1
2	1	aaaa	1_aaaa_x	1 & 2
3	2	aaaa	2_aaaa_y	2 & 1
4	3	bbbb	3_bbbb_x	3, 4 & 5
5	4	bbbb	4_bbbb_y	4, 3 & 5
6	5	bbbb	5_bbbb_z	5, 3 & 4
Data

Cell Formulas
Range	Formula
D2:D6	D2	=SUBSTITUTE(TEXTJOIN(", ",,A2,IF(($B$2:$B$10=B2)*($A$2:$A$10<>A2),$A$2:$A$10,"")),","," &",COUNTIFS($B$2:$B$10,B2)-1)

I am sorry. I guess it's on me that I never mentioned it in the first thread. But now I realized that this works for all the order has value more than one. But if it's only one order than it gives #VALUE! Error.