Add Zeros At End of String

[bilbo85]

Hi

I have some numbers that look like this:

186197.816492
184618.439
149183.4846
181861.816

I am looking for some code that will take the last 6 numbers after the decimal point and if there aren't 6 numbers then it should add zeros to the end (to make it to 6). So it will look like this:

816492
439000
484600
816000

There will always be 6 numbers before the decimal point and 6 or fewer after the decimal point.

Thanks in advance.

[mana]

=INT(1000000*MOD(A1,1))

[bilbo85]

This seems to return:

816492
439000
484599
815999

so it's not quite what I am after. Thanks though. Any other suggestions? Also, it needs to be a macro please.

[jolivanes]

This would be a formula to not have the dreaded 9's at the end
=RIGHT(A1,LEN(A1)-FIND(".",A1))&REPT("0",6-LEN(RIGHT(A1,LEN(A1)-FIND(".",A1))))

If it needs to be code, where does the result need to be? Replace the value in the cell or in a cell to the right one column, two columns or whatever?

[bilbo85]

Yes, it needs to replace the values in the cells if it's possible.

[jolivanes]

Try this on a copy of your workbook.

Sub Maybe_A()
Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    c.Value = CStr(Trim(Split(Format(c.Value, "###0.000000"), ".")(1)))
Next c
End Sub

I hope your cheering for the right team. Copper and Blue

[bilbo85]

Awesome that's exactly what I needed, thanks very much.

Canucks all the way, baby!

[jolivanes]

Although you're cheering for the wrong team, here is another possibility.
Just read that the number always has 6 digits before the decimal.
This should work also.

Sub Maybe_B()
Dim c As Range
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    c.Value = CStr(Mid(Format(c.Value, "###0.000000"), 8, 99))
Next c
End Sub