+ Reply to Thread
Results 1 to 4 of 4

Reduce variable string to comma separated string

  1. #1
    Registered User
    Join Date
    03-15-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Reduce variable string to comma separated string

    Hi all

    I have an issue I hope you can help me with.

    I have a series of strings that I need to convert to a comma separated.

    The strings could look like the following:
    * * * * * 2 * E * * * 3550 * BDF099 LKHAKJSHD1827 * * * 27.04.2017
    * * * * 2 * E * 4100 * AND123 JKAHGJKA123 * 29.06.2019
    * * * 4 * E * 4100 * DUC151 NBAJH12341234 * 16.07.2018 *URGENT
    * * * * 2 * E * 4100 * MBJA SAKLLDK12314 * 29.06.2019
    * * 2 * E * 4100 * KAJ987 AKJGHAJKH12783 * 29.06.2019
    Please notice that * = space (according to how excelforum.com handles multiple spaces)

    What I am looking for is:
    2,E,3550,BDF099,LKHAKJSHD1827,27.04.2017
    2,E,4100,AND123,JKAHGJKA123,29.06.2019
    4,E,4100,DUC151,NBAJH12341234,16.07.2018,URGENT
    2,E,4100,MBJA SAKLLDK12314,29.06.2019
    2,E,4100,KAJ987,AKJGHAJKH12783,29.06.2019

    I have tried several solutions but without luck.
    Hopefully one of you is able to help me out.

    Thanks in advance!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,919

    Re: Reduce variable string to comma separated string

    Welcome to the forum.

    Perhaps this:

    =SUBSTITUTE(TRIM(A1)," ",",")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    03-15-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Reduce variable string to comma separated string

    Thank you!
    I did try TRIM earlier but without luck - but it seems like my spaces were not recognized by TRIM (perhaps since it's exported from an old mainframe system).

    But I first did a substitute from the wrong space to a space recognized by TRIM, and then it worked.

    Thanks!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,919

    Re: Reduce variable string to comma separated string

    Fab - glad it worked.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Comma separated string element count
    By icyrius in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-30-2017, 10:58 AM
  2. Sort comma separated string, but in specific order
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2017, 08:53 AM
  3. [SOLVED] Substitute alphanumeric string into a comma separated string
    By icyrius in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-09-2017, 07:16 AM
  4. [SOLVED] Extract Numbers from Comma-Separated String
    By k64 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-17-2017, 06:30 PM
  5. Formula help on comma separated String data?
    By JGTExcel in forum Excel General
    Replies: 5
    Last Post: 02-10-2012, 01:05 PM
  6. Formula help on comma separated String data?
    By JGTExcel in forum Excel General
    Replies: 1
    Last Post: 02-09-2012, 03:33 AM
  7. Comma separated string
    By ESF in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-20-2011, 06:39 AM

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