PDA

View Full Version : Add Zeros At End of String



bilbo85
04-07-2017, 04:22 PM
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
04-07-2017, 04:59 PM
=INT(1000000*MOD(A1,1))

bilbo85
04-07-2017, 07:54 PM
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
04-07-2017, 08:46 PM
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
04-07-2017, 08:51 PM
Yes, it needs to replace the values in the cells if it's possible.

jolivanes
04-07-2017, 10:08 PM
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
04-07-2017, 10:44 PM
Awesome that's exactly what I needed, thanks very much.

Canucks all the way, baby!

jolivanes
04-07-2017, 10:51 PM
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