Hi Everyone,

If I have a Number in Cell "A1" of say 312, how can I Return the
Individual Numbers into 3 Seperate Cells.
I Know if I want the Last Number ( 2 in the Example ) I can Use :-
=MOD(A1,10)
What would I Use to get the 3 and the 1 Please.
Or is there a Formula that will Split the 3 Numbers and Add them
Together to give me a Total of ALL 3 Digits :-
3 + 1 + 2 = 6

Thanks in Advance
All the Best
Paul

Hi Paul

personally i'ld use data / text to columns
(ensure you have two blank columns to the right of your data)
select your cell(s)
choose data/ text to columns - next
choose fixed width - next
click on the little ruler between each digit - finish

Then to add them again, just use a normal formula in the next cell
=SUM(A1:C1)

Cheers
JulieD


"Paul Black" wrote in message
...
Hi Everyone,

If I have a Number in Cell "A1" of say 312, how can I Return the
Individual Numbers into 3 Seperate Cells.
I Know if I want the Last Number ( 2 in the Example ) I can Use :-
=MOD(A1,10)
What would I Use to get the 3 and the 1 Please.
Or is there a Formula that will Split the 3 Numbers and Add them
Together to give me a Total of ALL 3 Digits :-
3 + 1 + 2 = 6

Thanks in Advance
All the Best
Paul

Type 123456 in cell A10.

Type the following formula in cell B10:
=SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))

Press CTRL+SHIFT+ENTER to enter the formula as an array
formula.

(cited from MS support pages)

HTH,
Bernd

With your number in A10, in B10

=IF(COLUMN($A10)<=LEN(A10),MID($A10,COLUMN(A10),1) ,"")

and copy across as much as you want

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everyone,

If I have a Number in Cell "A1" of say 312, how can I Return the
Individual Numbers into 3 Seperate Cells.
I Know if I want the Last Number ( 2 in the Example ) I can Use :-
=MOD(A1,10)
What would I Use to get the 3 and the 1 Please.
Or is there a Formula that will Split the 3 Numbers and Add them
Together to give me a Total of ALL 3 Digits :-
3 + 1 + 2 = 6

Thanks in Advance
All the Best
Paul

Modification so that you can add them

=IF(COLUMN(B10)<=LEN($A10)+1,--MID($A10,COLUMN(A10),1),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
With your number in A10, in B10

=IF(COLUMN($A10)<=LEN(A10),MID($A10,COLUMN(A10),1) ,"")

and copy across as much as you want

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Black" wrote in message
...
Hi Everyone,

If I have a Number in Cell "A1" of say 312, how can I Return the
Individual Numbers into 3 Seperate Cells.
I Know if I want the Last Number ( 2 in the Example ) I can Use :-
=MOD(A1,10)
What would I Use to get the 3 and the 1 Please.
Or is there a Formula that will Split the 3 Numbers and Add them
Together to give me a Total of ALL 3 Digits :-
3 + 1 + 2 = 6

Thanks in Advance
All the Best
Paul

Thanks to Everyone for the Replies,

I am going to Use the One Posted by Bernd as this Makes my Task
Achievable in One Column Using One Formula ( Array Entered ) :-
=SUM(VALUE(MID(A10,ROW($A$1:OFFSET($A$1,LEN(A10)-1,0)),1)))

Bob, out of Interest, what is it about the Formula :-
=IF(COLUMN($A10)<=LEN(A10),MID($A10,COLUMN(A10),1) )
that Does NOT Allow you to do a Sum.

The Formula :-
=IF(COLUMN(B10)<=LEN($A10)+1,--MID($A10,COLUMN(A10),1),"")
Works and Does Allow you to Sum, But Unfortunately Needs Extra Columns
for the Information and One Further Column for the Sum Total.

Thanks to Everyone for their Time and Effort.

All the Best
Paul

Paul,

it returns a text answer, so SUMIng these gives a #VALUE. The -- coerces the
text into a number, which can then be SUMed.

BTW, I agree Bernd's is the one to use, but it is nice to have choice :-)

Bob


"Paul Black" wrote in message
. ..
Thanks to Everyone for the Replies,

I am going to Use the One Posted by Bernd as this Makes my Task
Achievable in One Column Using One Formula ( Array Entered ) :-
=SUM(VALUE(MID(A10,ROW($A$1:OFFSET($A$1,LEN(A10)-1,0)),1)))

Bob, out of Interest, what is it about the Formula :-
=IF(COLUMN($A10)<=LEN(A10),MID($A10,COLUMN(A10),1) )
that Does NOT Allow you to do a Sum.

The Formula :-
=IF(COLUMN(B10)<=LEN($A10)+1,--MID($A10,COLUMN(A10),1),"")
Works and Does Allow you to Sum, But Unfortunately Needs Extra Columns
for the Information and One Further Column for the Sum Total.

Thanks to Everyone for their Time and Effort.

All the Best
Paul

It's Me Again,

Is there Anyway to Adapt the Formula ( Array Entered ) :-
=SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))
So if I had :-
A10 = 9
B10 = 13
C10 = 20
D10 = 32
E10 = 41
F10 = 46
it would Still Work.
I know that I Could Concatenate A10:F10 and then Apply the Formula,
But it would be Nice if it Could be Done Within One Formula.
It is Basically the Sum of ALL Digits from A10:F10.

Thanks Again.
All the Best
Paul

Has Anybody got Any Ideas on How to Solve this Please.

All the Best
Paul



(Paul Black) wrote in message m...
It's Me Again,

Is there Anyway to Adapt the Formula ( Array Entered ) :-
=SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))
So if I had :-
A10 = 9
B10 = 13
C10 = 20
D10 = 32
E10 = 41
F10 = 46
it would Still Work.
I know that I Could Concatenate A10:F10 and then Apply the Formula,
But it would be Nice if it Could be Done Within One Formula.
It is Basically the Sum of ALL Individual Digits from A10:F10.

Thanks Again.
All the Best
Paul

Or is there a Formula that will Split the 3 Numbers and Add them
Together to give me a Total of ALL 3 Digits :-
3 + 1 + 2 = 6

To keep adding the individual digits in a repeating fashon to get a sum I
believe the formula is...
=MOD(A1-1,9)+1

=MOD(312-1,9)+1
returns 6

HTH
--
Dana DeLouis
Win XP & Office 2003


"Paul Black" wrote in message
...
Hi Everyone,

If I have a Number in Cell "A1" of say 312, how can I Return the
Individual Numbers into 3 Seperate Cells.
I Know if I want the Last Number ( 2 in the Example ) I can Use :-
=MOD(A1,10)
What would I Use to get the 3 and the 1 Please.
Or is there a Formula that will Split the 3 Numbers and Add them
Together to give me a Total of ALL 3 Digits :-
3 + 1 + 2 = 6

Thanks in Advance
All the Best
Paul

If they are at most 2 digit numbers, would this Array formula work?

=SUM(INT(A1:A6/10)+MOD(A1:A6,10))

--
Dana DeLouis
Win XP & Office 2003


"Paul Black" wrote in message
...
Has Anybody got Any Ideas on How to Solve this Please.

All the Best
Paul



(Paul Black) wrote in message
m...
It's Me Again,

Is there Anyway to Adapt the Formula ( Array Entered ) :-
=SUM(VALUE(MID(A10,ROW(A1:OFFSET(A1,LEN(A10)-1,0)),1)))
So if I had :-
A10 = 9
B10 = 13
C10 = 20
D10 = 32
E10 = 41
F10 = 46
it would Still Work.
I know that I Could Concatenate A10:F10 and then Apply the Formula,
But it would be Nice if it Could be Done Within One Formula.
It is Basically the Sum of ALL Individual Digits from A10:F10.

Thanks Again.
All the Best
Paul

Hmmm.... That doesn't look at all like Dana's formula. The one he posted was

=MOD(A1-1,9)+1

I put these numbers in A1:F1

492 12 259 158 314 54

If I use Dana's formula on each of these cell individually, the results are

6 3 7 5 8 9

The sum of those numbers is 38. Summing those digits, I get 11, then 2.

If 2 is the result that is wanted, the formula is the "normal" (i.e. not
array) formula

=MOD(SUM(A1:F1)-1,9)+1

If I use the formula you show below, the result I get is 155. To get 2, you
must sum the digits of that result.

BTW, using the worksheet MOD function, in the general situation, MOD(A1-1,9)+1
does not give NOT the same as MOD(A1,10). The two formulas give different
results when the original number is 0: 9 vs 0. (With VBA's MOD operator, the
results are both 0).


On 3 Dec 2004 05:55:38 -0800, (Paul Black) wrote:

The Second is to Sum the Individual Digits in a Range of Cells ( For
Example "A1:F1" ) Posted by Dana DeLouis :-
=SUM(INT(A1:F1/10)+MOD(A1:F1,10))
Array Entered.