Formula based solution to split number into separate columns

Question

999,888,777,666,555.82

In a1, the person may enter a number value up to trillions. I need to break down the number into separate columns.

In B1, the numbers to the right of the decimal.

C1, D1, E1, F1, G1. I need to put every three digits into each cell IF they exist. If the value isnt that high, just return blank in that column.

This has to be a macro-free workbook so, formulas only.

Thanks

Answer 1

Excel cannot handle 999,888,777,666,555.82 as a number - it has too many digits. If you try to enter it as a number, Excel will change it to 999,888,777,666,555.00 (the cents have been changed to 00).

So if you really need this, you must either format the cell as text before entering the value, or prefix it with an apostrophe/single quote.

In B1: =--MID(A1,FIND(".",A1)+1,2)

In C1: =INT(MOD($A1,1000))

In D1: =MOD(QUOTIENT($A1,1000),1000)

In E1: =MOD(QUOTIENT($A1,1000000),1000)

In F1: =MOD(QUOTIENT($A1,1000000000),1000)

In G1: =MOD(QUOTIENT($A1,1000000000000),1000)

In H1: =MOD(QUOTIENT($A1,1000000000000000),1000)

Answer 2

As always... Thanks my friend!

When I make my first million.... I'm buying you a Krystal WITH cheese, or white castle burger if you prefer. :-)