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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>