• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Very Challenging - Adding sequential string to CSV values in a single cell. Please Help.|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Very Challenging - Adding sequential string to CSV values in a single cell. Please Help.|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Very Challenging - Adding sequentia…
sp_PrintTopic sp_TopicIcon
Very Challenging - Adding sequential string to CSV values in a single cell. Please Help.
Avatar
Brenda Malone

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 29, 2021
sp_UserOfflineSmall Offline
1
January 30, 2021 - 1:42 am
sp_Permalink sp_Print

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!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
January 30, 2021 - 6:07 am
sp_Permalink sp_Print

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)?

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
3
January 30, 2021 - 8:07 am
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Brenda Malone

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 29, 2021
sp_UserOfflineSmall Offline
4
January 30, 2021 - 8:26 am
sp_Permalink sp_Print sp_EditHistory

Worked fantabulously, THANK YOU SO MUCH!

 

Can I buy you coffee??

Avatar
James McLeod

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 30, 2021
sp_UserOfflineSmall Offline
5
January 30, 2021 - 3:15 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
6
January 31, 2021 - 2:17 pm
sp_Permalink sp_Print

No need for coffee, we do this for fun.....

The attached has changed slightly to look for jpg, rather than just the comma

Avatar
James McLeod

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 30, 2021
sp_UserOfflineSmall Offline
7
January 31, 2021 - 9:03 pm
sp_Permalink sp_Print

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!! 

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 649
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
8
February 1, 2021 - 8:19 pm
sp_Permalink sp_Print

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
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
February 2, 2021 - 3:34 am
sp_Permalink sp_Print

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")&","

Avatar
Brenda Malone

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 29, 2021
sp_UserOfflineSmall Offline
10
February 2, 2021 - 3:42 am
sp_Permalink sp_Print

Thank you very much, @Purfleet and @Velouria!

Avatar
James McLeod

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
January 30, 2021
sp_UserOfflineSmall Offline
11
February 4, 2021 - 4:28 pm
sp_Permalink sp_Print

@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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 649
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
12
February 4, 2021 - 8:50 pm
sp_Permalink sp_Print

There is a Thanks button at the top right of each post (other than your own!):

 

MOTH-thanks-button.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage MOTH-thanks-button.png (138 KB)
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Louis Muti
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Blair Gallagher
Brandi Taylor
Hafiz Ihsan Qadir
Gontran Bage
adolfo casanova
Annestine Johnpulle
Priscila Campbell
Jeff Mikles
Aaron Butler
Maurice Petterlin
Forum Stats:
Groups: 3
Forums: 24
Topics: 6369
Posts: 27852

 

Member Stats:
Guest Posters: 49
Members: 32359
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.