#NUM! error when number is 13 digits or more

[memrani]

The following formula works fine as long as the integer in cell A1 has 12 digits or less. I get a #NUM! error otherwise (13 or more digits).
Any help is appreciated.

=MOD((A1-MOD(A1,POWER(10,ROW(A2)-2)))/POWER(10,ROW(A2)-2),10)

 

[jbeaucaire]

Can you provide some example values/results desired? Perhaps we can offer a different method.

 

[barry houdini]

I'm not quite sure what you are trying to do but.....

.....MOD function has some limitations when used with large numbers - try this version with FLOOR and RIGHT instead

=RIGHT(FLOOR(A1,POWER(10,ROW(A2)-2))/POWER(10,ROW(A2)-2))+0

 

[Scott Huish]

Here's a shorter version that I think will do the same thing:

=IF(ROW(A1)<=LEN(A1),LEFT(RIGHT(A1,ROW(A1)))+0,0)

 

[Scott Huish]

Excel Workbook
	A	B
1	123456789012345	5
2	123456789012345	4
3	123456789012345	3
4	123456789012345	2
5	123456789012345	1
6	123456789012345	0
7	123456789012345	9
8	123456789012345	8
9	123456789012345	7
10	123456789012345	6
11	123456789012345	5
12	123456789012345	4
13	123456789012345	3
14	123456789012345	2
15	123456789012345	1
Sheet1

 

[memrani]

Thank you all for the quick feedbacks. I do appreciate that and I am all set.

Cheers.