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]

Hi & welcome to MrExcel.
Will the text between the 2 "_" always be 4 characters?
Also can the number of digits before the 1st "_" vary?

 

[gargilang]

Hi & welcome to MrExcel.
Will the text between the 2 "_" always be 4 characters?
Also can the number of digits before the 1st "_" vary?

Thank you for warm welcome and reply.
To answer your first question, no. But it will be always either 4 or 5 characters. I am using another column to get that information as well with below formula:

=SUBSTITUTE(MID(A2,FIND("_",A2,1)+1,5),"_","")

And to your second question, yes. It is basically an order number that I am trying to get, from the table above, if item "aaaa" has 2 orders, it's Order 1 & 2. Item "bbbb" has 3 orders, order 3, 4 & 5. And let's say item "zzzz" has 4 orders, it could be order 101, 102, 103 & Pkg1.

I also attached a screenshot from the actual file that I am using. Hope it helps.

 

[Fluff]

Ok, how about

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

Cell Formulas
Range	Formula
B2:B6	B2	=LET(r,$A$2:$A$6,p,FIND("_",r),m,MID(r,p+1,100),mr,MID(A2,FIND("_",A2)+1,100),f,FILTER(LEFT(r,p-1),LEFT(m,FIND("_",m)-1)=LEFT(mr,FIND("_",mr)-1)),l,LEFT(A2,FIND("_",A2)-1),SUBSTITUTE(TEXTJOIN(", ",,l,FILTER(f,f<>l)),","," &",ROWS(f)-1))

 

[gargilang]

T

Ok, how about

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

Cell Formulas
Range	Formula
B2:B6	B2	=LET(r,$A$2:$A$6,p,FIND("_",r),m,MID(r,p+1,100),mr,MID(A2,FIND("_",A2)+1,100),f,FILTER(LEFT(r,p-1),LEFT(m,FIND("_",m)-1)=LEFT(mr,FIND("_",mr)-1)),l,LEFT(A2,FIND("_",A2)-1),SUBSTITUTE(TEXTJOIN(", ",,l,FILTER(f,f<>l)),","," &",ROWS(f)-1))

Thank you so much! It looks exactly like I wanted. But do you think there's something wrong with my excel? I tried to copy paste the formula but it gives me an error.

 

[Fluff]

Do you normally use commas as a separator in formulae, or do you normally use semi-colons?

 

[gargilang]

I am using commas. None of my formula using semi-colon.

 

[gargilang]

Do you normally use commas as a separator in formulae, or do you normally use semi-colons?

I am using commas. None of my formula using semi-colon.

 

[Fluff]

Ok, what if you copy the whole mini-sheet (using the copy icon) & paste it into a blank sheet?

 

[gargilang]

Ok, what if you copy the whole mini-sheet (using the copy icon) & paste it into a blank sheet?

View attachment 67307

I think I know why it's giving me an error. My corporate Excel doesn't recognize the LET Function. So I tried it on my personal PC and it works.
Is there any other method I could use other than the LET function? Does VBA or other excel features can do the same?
Also another thing that I found with your formula is that it will only work in certain range of cell, if the cell is empty then it gives #VALUE! error if I try to increase the range ($A$2:$A$100). Since my number of rows could be change from time to time, I would need this in automaton just like my other formulas.
But it is okay if not possible. Thank you so much for all the help and the answers.