I'm wondering if there is a function that will simplify adding each
individual digit in a number string. Here's an example:
Input 123 --- formula 1 + 2 + 3 --- result 6

I could parse the number using left, right and mid functions but I'm
wondering if there is any easier approach.

Thanks for the ideas.

- John

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John Michl" wrote in message
oups.com...
I'm wondering if there is a function that will simplify adding each
individual digit in a number string. Here's an example:
Input 123 --- formula 1 + 2 + 3 --- result 6

I could parse the number using left, right and mid functions but I'm
wondering if there is any easier approach.

Thanks for the ideas.

- John

Bob - this was exactly what I was looking for. I'm a big user of
SUMPRODUCT but never would have thought about using it this way. Still
not completely sure how it works but it does produce the correct
results.

Thanks!

- John

For your info John, it uses the length of the cell to pass to INDIRECT,
which is passed to ROIW, ROW(INDIRECT("1:"&LEN(A1)), which creates an array
of 1:n, where n is the length of A1. this is effectively used to extra each
digit of A1 (MID(A1,{1,2,...,n},1), in an array, which SP sums.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John Michl" wrote in message
oups.com...
Bob - this was exactly what I was looking for. I'm a big user of
SUMPRODUCT but never would have thought about using it this way. Still
not completely sure how it works but it does produce the correct
results.

Thanks!

- John

One way would be to do Data TextToColumns Fixed, and split each digit out
into it's own column and then just summing horizontally......

Vaya con Dios,
Chuck, CABGx3



"John Michl" wrote:

I'm wondering if there is a function that will simplify adding each
individual digit in a number string. Here's an example:
Input 123 --- formula 1 + 2 + 3 --- result 6

I could parse the number using left, right and mid functions but I'm
wondering if there is any easier approach.

Thanks for the ideas.

- John