Separating Numbers into Thousands, Hundreds, Tens and Units in Excel 2010 VBA

• Question

• Hello,

Can you please help me to find the way of splitting number into Thousands, Hundreds, Tens and Units, etc.?

For example I have 1485,3 in cell A1 and I would like to make the routine that splits this number in column "B" like that:

B1: 1000

B2:  400

B3:   80

B4:    5

B5:    3

Saturday, November 28, 2015 7:21 PM

All replies

• Try just using the MOD formula.

=MOD(A1,1000) returns 485,3

So what do you want to have happen in B1 if the number is 11453,0 ?

What if its only 485,3  ?

Saturday, November 28, 2015 7:53 PM
• Good question :-) That is what I was asking for. The number might even be integer.

I would like to split any number into Units, Tens, Hundreds, Thousands and so on...

Is it possible to do like that?

Saturday, November 28, 2015 7:57 PM
• In B1 units: =MOD(A1,10)

In B2 tens: =MOD(A\$1-SUM(B\$1:B1),10^ROW())

Fill down from B2 for hundreds, thousands etc.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Proposed as answer by Wednesday, December 2, 2015 3:57 AM
Saturday, November 28, 2015 10:05 PM