how do I total the value of the cell to a single value, 37=3+7=10

H

huong

Is there a function like substring in excel or something similar like that.
I would like to add all the digits in a cell to a single value, how would I
do that?

Thank you very much,

huong
 
R

RyanH

click the cell and type an equal sign then a number then a plus sign, like so

=5+5+4+3
cell should equal 17
 
S

smartin

huong said:
Is there a function like substring in excel or something similar like that.
I would like to add all the digits in a cell to a single value, how would I
do that?

Thank you very much,
Click to expand...

Hello huong,

There is a MID function (see F1), but you would have to write two
stand-alone MIDs (or more, depending on the length of your input) to
accomplish what you want.

The following works for any reasonable length of input. It is an array
formula, so you must press Ctrl+Shift+Enter to commit:

=SUM(VALUE(MID(A1,ROW(INDIRECT("x1:x"&LEN(A1))),1)))

(Idea stolen from Pete_UK -- all props to him!)
 
G

Gord Dibben

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

37 returns 10

123 returns 6


Gord Dibben MS Excel MVP
 
R

Rick Rothstein

Try this array-entered** formula...

=SUM(IF(ROW(1:99)<=LEN(A1),--MID(A1,ROW(1:99),1),""))

** Commit this formula with Ctrl+Shift+Enter, not just Enter by itself.

Note: If you enter your digits as text, the above formula will add up to the
first 99 digits. If you would want more than that, just change both 99's to
the same higher digit limit.
 
Top