Help with Similar MOD Function Please

[Paul Black]

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

 

[JulieD]

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

 

[Bernd]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Paul Black]

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

 

[Bob Phillips]

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]

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

 

[Paul Black]

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

All the Best
Paul

 

[Dana DeLouis]

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]

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

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

 

[Paul Black]

I would like to Thank EVERYONE who Contributed to Solving my Request.
There are Two Formulas that Suit my Needs and I will be Using.

The First is to Sum the Individual Digits in a Single Cell ( For
Example "A1" ) Posted by Bernd :-
=SUM(VALUE(MID(A1,ROW($A$1:OFFSET($A$1,LEN(A1)-1,0)),1)))
Array Entered - The "$A$1" Does NOT Change, ONLY the "A1" Changes.

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.

By the way Bob, I could NOT get Either of your Formulas to Work for
Some Reason :-
=IF(COLUMN($A1)<=LEN(A1),MID($A1,COLUMN(A1),1),"")
=IF(COLUMN(B1)<=LEN($A1)+1,--MID($A1,COLUMN(A1),1),"")
Thanks for the Explanation of why the First One could NOT be Copied. I
Always Wondered what the Two Dashes were for.

Thank you ALL again.
All the Best
Paul

 

[Myrna Larson]

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