Combining Digits

[John Michl]

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 Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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]

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

 

[Bob Phillips]

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)