Shorter version (still array formula):

=SUM(--("0"&MID(A1,ROW(1:99),1)))+LEN(SUBSTITUTE(A1,"."," "))


Glenn wrote:
The following array formula (commit with CTRL+SHIFT+ENTER) will work:

=SUM(--MID(SUBSTITUTE(A1,".",""),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,".","")))),1)) +
LEN(SUBSTITUTE(A1,".",""))


christine mullholand wrote:
Did you ever find a function for the verification code? I also need
that.



Olivi wrote:

Formula for each # of dollar amount due + how many digits
06-Mar-08

I need some help creating a function or formula in Excel for the
following:

??? Example: payment amount is $2,589.32 Run a tape:
2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) = 35 --- 35 is
your verification code.
Is there a formula or function in excel, so that this will
automatically populate once we enter a payment amount in a different
cell.

Thanks

Previous Posts In This Thread:

On Thursday, March 06, 2008 12:34 PM
Olivi wrote:

Formula for each # of dollar amount due + how many digits
I need some help creating a function or formula in Excel for the
following:

??? Example: payment amount is $2,589.32 Run a tape:
2+5+8+9+3+2 + 6 (how many numbers you have in $ amt) = 35 --- 35 is
your verification code.
Is there a formula or function in excel, so that this will
automatically populate once we enter a payment amount in a different
cell.

Thanks

On Thursday, March 06, 2008 2:13 PM
Bob Phillips wrote:

Formula for each # of dollar amount due + how many digits
=SUM(IF(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))+COUNT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))


which is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly
brackets), do not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to
excel 2007), but must use an explicit range.