How to remove a decimal but retain the value of the number

Question

I need to convert a "column of numbers", say 342.21 into 34221 with a number of leading zeroes. Expected end result would be 0000000000034221. What specific format or code should I use in vba to get this?

I am trying to convert an excel file to text file using vba. The field length for the end result is fix at 16 digits and length for the "column of numbers" may vary. Hope you can help me.

Thank you very much.

Answer 1

You mention a string:

=RIGHT("0000000000000000"&SUBSTITUTE(B1,".",""),16)

Will result in a string that will export to csv includiong the 0s

Answer 2

Use

ActiveSheet.Range("A1").NumberFormat = "@"
ActiveSheet.Range("A1").Value = Format(ActiveSheet.Range("A1").Value * 100,"0000000000000000")

Answer 3

Since you are converting to a string, we can just treat your number value as a string.

Assuming your number is stored in variable num:

outputnumber = String(16 - (Len(num) - 1), "0") & Replace(num, ".", "")

Answer 4

You can use the Custom Format type: 0000000000000000 (That's 16 zeroes)

Then in the cell, you could do =A1*100 assuming A1 has 342.21.

(Edit: Sorry, missed the VBA tag. The above doesn't use VBA).

Answer 5

So I tried a simplified code:

    Amount = (.Cells(iRow,6)) - where I store my values
    Amount = Format(Amount*100, "0000000000000000")

Thank you so much for the help guys! :)