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

  • 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  ?

    Rod Gill
    Author of the one and only Project VBA Book

    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?

    Thank you for your assistance.

    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 (

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