+ Reply to Thread
Results 1 to 8 of 8

Insert commas into a row/cell of words

  1. #1
    Registered User
    Join Date
    04-15-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Insert commas into a row/cell of words

    Background info: I have access to Excel 2007. I am using it because I need to move data from one website another website and the content management systems will allow export/import using csv files. Everything is okay except for one column holding tags in the exported file.

    The tags for each piece of content were exported into a column of rows like so:

    word1 word2 word3
    word1 word2
    word1 word2 word3 word4
    word1 word2
    word1

    The import function on the second site expects the tags to be separated by commas. I did a bit of reading online but the examples I've found don't help my situation because a lot of the tags are not single word tags. Doing a simple replace of spaces with commas would not produce the correct data.

    I can get an export of the tags separated into one per row like so:

    tag1
    tag2a tag2b [this indicates a multi-word tag]
    tag3
    tag4a tag4b tag4c
    tag5a tag5b
    tag6
    etc.

    I am wondering if an excel function could look at each row/cell of tags separated by spaces and look at the full list of separate tags and then insert a comma after any single/muti-word match found so data would look like so:

    word1 word2, word3
    word1, word2
    word1 word2, word3 word4
    word1 word2
    word1

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Insert commas into a row/cell of words

    Welcome to the forum.

    How do you know the difference between words and tags?

    How about posting a workbook with and example of input and corresponding output, and an explanation of the algorithm you applied?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-15-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Insert commas into a row/cell of words

    I've attached an example of what I have in mind. Sheet 1 is how the data currently looks, sheet 2 would be the list of tags and sheet 3 is how the data should look after the commas are inserted.

    @shg - tags and words are the same things, tags are made up of single word or multiple words.
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Insert commas into a row/cell of words

    Do all the words on sheet 1 always appear on sheet2?

  5. #5
    Registered User
    Join Date
    04-15-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Insert commas into a row/cell of words

    Yes, nothing is being changed on the old site so the data is static.

    Sheet2 is a list of all the tags attached to all the content. Each row in sheet1 represents one piece of content and the specific tags for that piece of content from the list in sheet2.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Insert commas into a row/cell of words

    Try this:
    Please Login or Register  to view this content.
    Last edited by shg; 04-18-2010 at 02:30 PM. Reason: minor optimizations

  7. #7
    Registered User
    Join Date
    04-15-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Insert commas into a row/cell of words

    Thanks for the help. I tried this out on the sample workbook and it worked like a charm. I will try it out on the actual data. Hope it works just as well on that too.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Insert commas into a row/cell of words

    Good, let me know how it turns out.

    If you have a lot of data, there may be some optimizations.
    Last edited by shg; 04-18-2010 at 02:13 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1