« Inputbox Validation
Daily Dose Headlines »

Summing Comma Separated Values in a Cell

4th May 2005, 10:37 am by Dick Kusleika

Created by David Hager

To add comma delimited values in a cell (such as 1,2,11,4 in cell A1) to
the right of the cell containing the string, highlight cell B1 (for this
case) and create the following defined name formula (called “csum”):

=EVALUATE(SUBSTITUTE(A1,”,”,”+”))

Then, type =csum in B1 to obtain the result (18, in this case).

Defined name dialog for csum

Range showing defined name, csum, in formula

Category: Names, String Functions, Excel Experts E-Letter  |  Comment (RSS)  |  Trackback

3 Comments

  1. Jason Morin:

    When I learned about XLM and the ability to use it through defined names in Excel, I thought I was in heaven. It was a blessing because I could accomplish many of the same things as UDFs, but I didn’t have to know VBA! The amount of information you could obtain from GET.CELL alone was incredible.

    But as time passed and my VBA skills improved (improved is a relative term), I shied away from it. But I also heard a rumor from one of the MVPs (don’t recall who) that Microsoft will eventually phase it out of Excel…another good reason to stay away from solutions using XLM.

    Is this true? If so, in what version will this happen?

    Thanks.
    Jason

    5 May 2005, 12:47 pm

  2. David Hager:

    If it is going to happen, it might not be a bad idea for the MVPs to request that some of the useful xlm functions be converted to native Excel functions. It is likely a trivial process for the Excel development team to do this, but it is not a trivial process to put the request in their headlights.

    5 May 2005, 1:24 pm

  3. Chris:

    You can automatically make the link a url with:

    =HYPERLINK(CONCATENATE(A1, A2))

    13 December 2005, 6:49 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment