+ Reply to Thread
Results 1 to 8 of 8

Concatenate a list of indefinite length with NO COMMA at the end

  1. #1
    Registered User
    Join Date
    11-16-2010
    Location
    Minneapolis
    MS-Off Ver
    Excel 2003
    Posts
    4

    Concatenate a list of indefinite length with NO COMMA at the end

    Hello everyone! I just registered at this site to post this question, so please go easy on me.

    I'm trying to prepare a spreadsheet for my personal workload management. I've constructed a ridiculously lengthy CONCATENATE formula filled with IFs and IF(AND)s and other CONCATENATEs to make a nice single-cell list of items I need, along with the date(s) when I ordered them. I use logic functions to put "Item Names" together with "Order Dates" whenever "Order date" is populated and "Received Date" is not. This "summary" forumla will sit in a header cell above tables stored on multiple worksheets. A summary worksheet will refer to these summary cells so that I can print a single worksheet and know what is missing on each list, and also know how long I've been waiting.

    The problem is that I want it to be presentable and easily understood, and I don't want to have to handle questions about that pesky dangling comma at the end of the list. The trick here is that I never know how long the list will be. I'm going to build the list to be 20 items long and assume I'm safe. My typical list is three to six items. Very rarely does this list break 10. In this example, I'll use a list of that's only five rows long, but I'll only be after four items.

    I need Items One through Four, so those text strings go in cells A1-A4. Order dates go in cells B1-B4 and Received dates go in cells C1-C4. The formula that I've written works well logically, but I can't fix "the comma problem" because the formula also considers the contents of cells A5-C5. In this example, if I've received Item One and Item Three, my Missing Items summary formula produces either this string:

    Item Two (Ordered 11/1/10), Item Four (Ordered 11/1/10),
    ...which ends with comma space, or this string:

    , Item Two (Ordered 11/1/10), Item Four (Ordered 11/1/10)
    ...which begins with comma space.

    Does anybody know a quick and easy way to get rid of this silly comma? I've only had one idea: I could use the first output string (ending in comma space) and delete the last two characters. I would use two functions for this: The first function would count up the characters in the string and produce value X. The second function would duplicate the first X-minus-two characters of the string, and the comma should be gone. I forget the names of these functions but I have read about them so I know they're there.

    Am I missing a more elegant solution here? I would like a formula that filters away the items that have Received Dates, and then concatenates Item Names and Order dates for the remainder of the list. I just don't know if this is possible without writing a macro, which is a whole different ball of wax obviously. Thoughts?

  2. #2
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Smile Re: Concatenate a list of indefinite length with NO COMMA at the end

    It would appear that your situation has an easy solution but it would be better if you could attach a non-confidential file to demonstrate your question.
    Turn Data into Information
    Turn Information into Knowledge
    Turn Knowledge into Direction
    Turn Direction into Leadership
    Turn Leadership into Results
    Stephen Druley

    It's not how quickly you think
    But how deeply you think
    The quality of thinking is measured
    by remoteness to conformance
    Stephen Druley

  3. #3
    Registered User
    Join Date
    11-16-2010
    Location
    Minneapolis
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Concatenate a list of indefinite length with NO COMMA at the end

    That might be possible, but I'm iffy on doing that from the office. I'd just rebuild the thing at home if I had Excel! I only have Openoffice on my PC at home, and the syntax seems different. It wants me to use lots of semicolons, anyway.

  4. #4
    Registered User
    Join Date
    11-16-2010
    Location
    Minneapolis
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Concatenate a list of indefinite length with NO COMMA at the end

    Bump for help without uploading an attachment...???

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Concatenate a list of indefinite length with NO COMMA at the end

    Use the version which gives you a comma, space at the start and wrap the formula in a SUBSTITUTE function to remove just the first instance of that, i.e.

    =SUBSTITUTE(formula,", ","",1)
    Audere est facere

  6. #6
    Registered User
    Join Date
    11-16-2010
    Location
    Minneapolis
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Concatenate a list of indefinite length with NO COMMA at the end

    Thank you, that's a great suggestion! Unfortunately, it seems to present its own issue: It removes the first instance of comma-space in the string regardless of where it appears. In my example of a four-item list on a five-row table, lets say I ordered all four items on November 1st and I've received none of them. Wrapping my summary forumla with the SUBSTITUTE function produces a string like this:

    Item One (Ordered 11/1/10)Item Two (Ordered 11/1/10), Item Three (Ordered 11/1/10), Item Four (Ordered 11/1/10)
    Here, the comma-space has been removed from between Item One and Item Two, so the substitute-wrapped summary formula will only produce the perfect string when Item One is both ordered and received.

    Regardless, this could be a great step in the right direction. I'm going to look into using a logical test to determine if the substitution is necessary. I'll need to figure out if there's a function that can tell me if a string begins with comma-space. If true, perform summary formula wrapped in substitute function. If false, run summary formula without the substitution.

    Alternatively, I suppose I could put a comma-space BEFORE Item One understanding that it will be substituted every time. I'll have to tinker with this a bit. Thanks again for your suggestion!

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Concatenate a list of indefinite length with NO COMMA at the end

    trim(if(left(a1)=",",then substitute,else dont))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Concatenate a list of indefinite length with NO COMMA at the end

    Yes, I assumed you would always get a comma/space at the start (unless there's nothing to concatenate), e.g. if you want to concatenate B1:B5 only when A1:A5 has an "x" then

    =SUBSTITUTE(IF(A1="x",", "&B1,"")&IF(A2="x",", "&B2,"")&IF(A3="x",", "&B3,"")&IF(A4="x",", "&B4,"")&IF(A5="x",", "&B5,""),", ","",1)

+ 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