From column to cell based on starting number?

[MikeSK]

Hello,
I have one column with file names like this:

1000-x.jpg
1000-y.jpg
1000-z.jpg
2000-f.jpg
2000-g.jpg
3000-s.jpg
...

I would like to add text (the same one) in front of the text and transpose this column that way, so cells texts with the same starting number will be in one cell separated by comma like this:

mytext.1000-x.jpg, mytext.1000-y.jpg, mytext.1000-z.jpg
mytext.2000-f.jpg, mytext.2000-g.jpg
mytext.3000-s.jpg
...

What would be the best way to approach this?

Thank you for your time.

Mike

 

[Peter_SSs]

If you are looking for a formula approach, here is one way provided your version of Excel 365 has the FILTER & UNIQUE functions.

Formula in column C goes in cell C2 only and the other values will automatically 'spill' down to other cells
Formula in column D gets copied down as far as you might neeed.

MikeSK 2020-07-15 1.xlsm
	A	B	C	D
1
2	1000-x.jpg		1000	mytext.1000-x.jpg, mytext.1000-y.jpg, mytext.1000-z.jpg
3	1000-y.jpg		2000	mytext.2000-f.jpg, mytext.2000-g.jpg
4	1000-z.jpg		3000	mytext.3000-s.jpg
5	2000-f.jpg
6	2000-g.jpg
7	3000-s.jpg
8
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=UNIQUE(LEFT(A$2:A$7,FIND("-",A2:A7)-1))
D2:D6	D2	=IF(C2="","","mytext."&TEXTJOIN(", mytext.",1,FILTER(A$2:A$7,LEFT(A$2:A$7,LEN(C2))=C2)))
Dynamic array formulas.

 

[MikeSK]

Thank you Peter! It works perfectly!

 

[Peter_SSs]

Thank you Peter! It works perfectly!

Good news! Thanks for the follow-up.