Extract 9 last number from a number of 14 digit-CodePudding

One of my Excel column of my board have to store numbers of 9 digits. I'm looking for a solution to keep only the 9 last digits of any bigger number past in this specific column. It's only entire number.

Also if after formatting the number it appear that the number starts with 0 the 0 have to be kept. Is there another solution than adding an '0 at first ?

Here is what I already done : (i is the row number / Range01 is Range("A14:O400"))

If Len(Range01.Cells(i,5).value) = 9 Then
 Range01.Cells(i,5).Interior.color = vbGreen
ElseIf Len(Range01.Cells(i,5).value) = 8 Then
 Range01.Cells(i,5).value = "'0" & Range01.Cells(i,5).value
ElseIf Len(Range01.Cells(i,5).value) > 9 Then
 ????
Else
 Range01.Cells(i,5).Interior.color = vbRed
End If

Thanks for the help.

CodePudding user response：

The simplest way to get the last nine numbers of an integer is:

=MOD(A1,1000000000)

(For your information, that's one billion, a one with nine zeroes.)

If you're interested in showing a number with leading zeroes, you can alter the cell formatting as follows: (the format simply contains nine zeroes)

If you're interested in keeping the zeroes, you might need to use your number as a string, and precede it with a good number of repeated zeroes, something like:

=REPT("0",9-LEN(F8))&F8

Take the length of your number (which gets automatically converted into a string)
Subtract that from 9 (so you know how many zeroes you need)
Create a string, consisting of that number of zeroes
Add your number behind it, using basic concatenation.

CodePudding user response：

You can simply use the math operator of modulus. If you want the last 9 digit you can write:

n % 10000000000

Where n is the number in the column. In VBA:

MOD(n,1000000000)