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).
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
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.
You can automatically make the link a url with:
=HYPERLINK(CONCATENATE(A1, A2))
how to add the following values in ms excel.
cell A= 1,2
cell B= 3,4
cell C= (first element of A i.e 1 + first element of B i.e.3) & (second element of A i.e 2 + second element of B i.e 4)