Log In
Register
Home
Active Member
January 29, 2021
Offline
Hi. I am trying to help a friend with this perplexing Excel. Not being a Excel Guru, I humbly ask for help here.
My normal concatenation and substitution formulas are just not cutting it. The sequential, advancing number has me stumped.
Essentially, this is what needs to happen:
UNIQUE IDENTIFIER (A1)
needs to be inserted into the string of data in (B1) before EVERY comma.
That string is a list of comma-separated JPG urls all in a SINGLE CELL.
AND
The UNIQUE IDENTIFIER has to be preceded by "::" and also increase by 1 at every insertion within that single cell.
| unique identifier | Images needing to be modified | What I hope it looks like (what I need it to be like to work :)) ) |
| Sku abc | one.jpg,two.jpg,three.jpg,four.jpg | one.jpg::sku_abc_001,two.jpg::sku_abc_002,three.jpg::sku_abc_003,four.jpg::sku_abc_004, |
THANK YOU for Any Paths/Suggestions!
See Answer
Trusted Members
December 20, 2019
Offline
What version of Excel are you running? is there an known upper limit of the jpg's in the image column (always 4 or maximum of 4)?
Trusted Members
December 20, 2019
Offline
I have wrote a little macro that seems to do what you want. It might need tweaking and i have added a Comma at the end of the text so it can count properly
Answers Post
Active Member
January 29, 2021
Offline
Worked fantabulously, THANK YOU SO MUCH!
Can I buy you coffee??
Active Member
January 30, 2021
Offline
Purfleet:
Thank you for the quick answer and excellent answer. It did an excellent job handling a large number of *.jpg files (the count(s) do vary from row-to-row and project-to-project) the macro worked great on the varying number.
Would it be possible to ask for a small modification to the macro. I am working with image URLs and if there is a period anywhere other than before “jpg” it throws up the debugger. For example: https://Imange.Domain.com/image_1.jpg
Brenda, recommended a work-around, where I replace the offensive periods with another non-html character and then change them back afterwards.
Other than that, the end result is exactly what I needed. Thank you again, Purfleet.
Brenda:
Thank you fo recommending this forum to me and asking this question on my behalf. You did an excellent job asking the question, it was very clear and to the point. I probably would have ended up being very wordy, I’m working on it.
Trusted Members
December 20, 2019
Offline
No need for coffee, we do this for fun.....
The attached has changed slightly to look for jpg, rather than just the comma
Active Member
January 30, 2021
Offline
Worked perfectly. Thank you so very much!!
I hope you have a great day.
Thank you, everyone. Brenda and Purfleet, the two of you helped me out so very much. I hope the balance of your weekend is well and the coming week too!!
Trusted Members
Moderators
November 1, 2018
Offline
Just as an alternative, you could also do it as a UDF:
Function AddSKU(CellText As String, SKU_text As String) As String
Const DELIMITER As String = ","
SKU_text = Replace$(SKU_text, " ", "_") & "_"
Dim parts
parts = Split(CellText, DELIMITER)
Dim counter As Long
For counter = LBound(parts) To UBound(parts)
parts(counter) = parts(counter) & "::" & SKU_text & Format$(counter + 1, "000")
Next counter
AddSKU = Join(parts, DELIMITER)
End Function
then enter say =AddSKU(B2, A2) in a cell and copy down.
The following users say thank you to Velouria for this useful post:
Purfleet
VIP
Trusted Members
June 25, 2016
Offline
Just to join in the fun, a formula alternative
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"jpg","jpg::sku_abc_00X"),"X","1",1),"X","2",1),"X","3",1),"X","4")&","
Active Member
January 29, 2021
Offline
Thank you very much, @Purfleet and @Velouria!
Active Member
January 30, 2021
Offline
@Velouria, this was a very interesting approach too. Another way to skin a cat, thank you for showing another way. I can definitely use both solutions. Thank you kindly.
@Purfleet, thank you again. your solution helped me very much as I had mentioned before.
As a side question, how did you add the following comment/text to the actual post of @Velouria:
The following users say thank you to Velouria for this useful post:
Purfleet
The above was interesting how it was slotted with their response. I'm just curious... I am not familiar with forums and how to do something like that.
@SunnyKow, thank you for your formula. While it doesn't seem to tackle the problem directly... I can see how that formula would be helpful for something else I could use.
@Brenda Malone - Thank you again for everything.
Everyone here is amazing with their skills. Excel and its power users never stops amazing me. Thank you for your assistance @all
1 Guest(s)
