+ Reply to Thread
Results 1 to 7 of 7

CONCATENATE function and extra commas

  1. #1
    Registered User
    Join Date
    08-25-2015
    Location
    Worcester
    MS-Off Ver
    2010
    Posts
    6

    CONCATENATE function and extra commas

    On the attached spreadsheet I can't get the concatenate function to join text strings without putting in surplus commas. In each helper cell on the right eg. CH4 I only want it to return the text from D3 if D4 is red and E3 if E3 is red etc. I can get it to work but there are extra commas when nothing is to be joined into the text string. It's not a major issue but a bit annoying as it doesn't look as good when mailmerging.

    Any help would be great.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: CONCATENATE function and extra commas

    Hi,

    Try the following in CH4:

    =TRIM(SUBSTITUTE(CONCATENATE((IF($D4="r",$D$3,"")), ", ",(IF($E4="r",$E$3,"")), ", ",(IF($F4="r",$F$3,"")))," ,",""))
    Last edited by cbatrody; 09-02-2015 at 08:05 AM.

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: CONCATENATE function and extra commas

    Please Login or Register  to view this content.
    Try these formulas and all other cells in the same way.
    And maybe een IFERROR in front of it.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: CONCATENATE function and extra commas

    Quote Originally Posted by popipipo View Post
    Please Login or Register  to view this content.
    Try these formulas and all other cells in the same way.
    And maybe een IFERROR in front of it.
    Hi Willem,

    There are multiple matches in some places, see CK5, CK6, CI4 etc..

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: CONCATENATE function and extra commas

    I had overlooked it

  6. #6
    Registered User
    Join Date
    08-25-2015
    Location
    Worcester
    MS-Off Ver
    2010
    Posts
    6

    Re: CONCATENATE function and extra commas

    Thanks for this, it works in some cases but then removes commas completely in others. I still need commas to separate each item in the text string but if there is only 2 items out of the 3 possible then not to have X,Y,,

    Thanks for all your help.

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: CONCATENATE function and extra commas

    Well, it does not remove commas from all places. See the below image:

    In CI4:
    =TRIM(SUBSTITUTE(CONCATENATE((IF($G4="r",$G$3,"")), ", ",(IF($H4="r",$H$3,"")), ", ",(IF($I4="r",$I$3,"")),", ",(IF($J4="r",$J$3,"")))," ,",""))
    Attached Images Attached Images
    Attached Files Attached Files

+ 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] Export to CSV writes extra 130 lines of commas
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2015, 05:09 AM
  2. [SOLVED] Remote extra commas from Column
    By satputenandkumar0 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-21-2014, 08:54 AM
  3. Convert to CSV , extra commas displaying
    By yashu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-24-2013, 08:49 PM
  4. Create CSV without extra Commas
    By naveenkharb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2010, 10:56 AM
  5. how to stop excel from adding extra inverted commas?
    By podcast in forum Excel General
    Replies: 1
    Last Post: 07-27-2007, 06:49 PM
  6. Extra trailing commas in exported CSV
    By lucidr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2006, 08:40 AM
  7. Replies: 3
    Last Post: 06-15-2005, 06:05 PM

Tags for this Thread

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