Dale needs to upload listings to eBay. One of the tough fields is the Gallery field, where the names of several images need to be in a single cell separated by semi-colons (Bill1.jpg;Bill2.jpg;Bill3.jpg). Given two cells that contain the image prefix and the number of images, can an Excel formula generate this gallery list? It feels like there should be an array formula to do this, but I had to resort to a VBA user defined function to solve the problem.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn excel from MrExcel podcast Episode # 1865 ebay Gallery Multiple Image File Name Concatenate Hey welcome back to MrExcel netcast. I'm Bill Jelen.
Today this is a question that is probably faced by all ebay power sellers.
There's a utility upload your information to ebay and you have to create these two texts things.
The main image which has you know like for example, sku and then 1.jpg and then the media gallery. This is the one that's really really hard because there are 4 images and it's dale 1 dale 2 dale 3 dale 4 with ; in between and there could be anywhere from 1 to 30 images so the question is how do we build that string without simply just typing the whole thing you know and this is one yeah I talked to Mike Gervin and I said Mike would this be a good dueling Excel podcast you know because what we need to do here I've built a little formula that says you know take the base name and then concatenate with row 1 to 30 and then well I guess actually it's .jpg and that returns just the first item but if i do F2 F9 I actually get almost what I need. There's the extra quotes around there but you know boy if I guess I'll just return this string to a cell like if there's a way to use the text function or something like that at end if you're watching this you might know a way to do that but I couldn't figure it out.
So that means I'm switching over to what I always do i switch over to VBA.
Now a couple of things if you have an excel file and it's saved with this xls xlsx file extension that is the worst excel file extension there is.
It's the only file extension in the history of the world that doesn't allow Macros and unfortunately Microsoft makes this be the default so it's very likely that you have this file type.
If you do you're going to have to do File Save As and save it not as the thwarted xlsx but any of the real file types xlsm or xlsb. Any of those will work it's just that crazy xlsx that does not allow macro so save it that way.
Also if you've never done Macros before do Alt T followed by M followed by S and make sure that you're not set on that top one. You want to go at least to the second security setting there.
Alright so once we've done that Alt F11.
We're going to Insert a Module.
I'm just going to paste it here. Okay so here's some text letter wrote.
We have a function called Gallery. We're going to pass two things.
The base text, that's the file name the prefix file name and then how many times we want to repeat.
Now we have to have a variable that matches that name that returns answer at the end so we initialize that to quote quote for i = 1 to RepeatCount So however many pictures we have Gallery = to the old version of Gallery plus the BaseText then the counter and then the prefix .jpg with the ; Do Next i So if there's four of them it'll do you know, Bill 1 Bill 2 Bill 3 Bill 4 with semi-colons between each one and then finally to get rid of that last semicolon take the Left of the Gallery the entire length of the Gallery minus 1 and that will be returned.
So let's take a look at how that works.
So we just come here and say = gallery here's the base sku and how many times we want to repeat and it works perfectly.
If we would say for example, maybe 30 then it will repeat and we get our answer all the time.
How do we get the main image, well that's easy.
Just take the A3 & the number 1.jpg all in quotes and that one is the easiest part.
It's the media gallery that was tough and I'm really interested in the YouTube comments if we can come up with some sort of a formula that would do this so we wouldn't have to use VBA.
I would love love to see that.
I never use the more funky .add-in and I know they have it in m concate. I would guess that m concate would let us do this all.
I don't know if it handles an array and I've heard there's problems to have more funk work in the later versions of excel.
So I'm not sure that that is a good option but at least with this VBA we can solve it for now.
Hey I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
Learn excel from MrExcel podcast Episode # 1865 ebay Gallery Multiple Image File Name Concatenate Hey welcome back to MrExcel netcast. I'm Bill Jelen.
Today this is a question that is probably faced by all ebay power sellers.
There's a utility upload your information to ebay and you have to create these two texts things.
The main image which has you know like for example, sku and then 1.jpg and then the media gallery. This is the one that's really really hard because there are 4 images and it's dale 1 dale 2 dale 3 dale 4 with ; in between and there could be anywhere from 1 to 30 images so the question is how do we build that string without simply just typing the whole thing you know and this is one yeah I talked to Mike Gervin and I said Mike would this be a good dueling Excel podcast you know because what we need to do here I've built a little formula that says you know take the base name and then concatenate with row 1 to 30 and then well I guess actually it's .jpg and that returns just the first item but if i do F2 F9 I actually get almost what I need. There's the extra quotes around there but you know boy if I guess I'll just return this string to a cell like if there's a way to use the text function or something like that at end if you're watching this you might know a way to do that but I couldn't figure it out.
So that means I'm switching over to what I always do i switch over to VBA.
Now a couple of things if you have an excel file and it's saved with this xls xlsx file extension that is the worst excel file extension there is.
It's the only file extension in the history of the world that doesn't allow Macros and unfortunately Microsoft makes this be the default so it's very likely that you have this file type.
If you do you're going to have to do File Save As and save it not as the thwarted xlsx but any of the real file types xlsm or xlsb. Any of those will work it's just that crazy xlsx that does not allow macro so save it that way.
Also if you've never done Macros before do Alt T followed by M followed by S and make sure that you're not set on that top one. You want to go at least to the second security setting there.
Alright so once we've done that Alt F11.
We're going to Insert a Module.
I'm just going to paste it here. Okay so here's some text letter wrote.
We have a function called Gallery. We're going to pass two things.
The base text, that's the file name the prefix file name and then how many times we want to repeat.
Now we have to have a variable that matches that name that returns answer at the end so we initialize that to quote quote for i = 1 to RepeatCount So however many pictures we have Gallery = to the old version of Gallery plus the BaseText then the counter and then the prefix .jpg with the ; Do Next i So if there's four of them it'll do you know, Bill 1 Bill 2 Bill 3 Bill 4 with semi-colons between each one and then finally to get rid of that last semicolon take the Left of the Gallery the entire length of the Gallery minus 1 and that will be returned.
So let's take a look at how that works.
So we just come here and say = gallery here's the base sku and how many times we want to repeat and it works perfectly.
If we would say for example, maybe 30 then it will repeat and we get our answer all the time.
How do we get the main image, well that's easy.
Just take the A3 & the number 1.jpg all in quotes and that one is the easiest part.
It's the media gallery that was tough and I'm really interested in the YouTube comments if we can come up with some sort of a formula that would do this so we wouldn't have to use VBA.
I would love love to see that.
I never use the more funky .add-in and I know they have it in m concate. I would guess that m concate would let us do this all.
I don't know if it handles an array and I've heard there's problems to have more funk work in the later versions of excel.
So I'm not sure that that is a good option but at least with this VBA we can solve it for now.
Hey I want to thank you for stopping by.
Will see you next time for another netcast from MrExcel.
