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

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

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

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

--
Cheers,
Ryan


"huong" wrote:

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

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

huong wrote:
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,

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

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

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

37 returns 10

123 returns 6


Gord Dibben MS Excel MVP

On Wed, 3 Sep 2008 16:12:09 -0700, huong
wrote:

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

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

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.

--
Rick (MVP - Excel)


"huong" wrote in message
...
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