Creating an FTP file. need cell to be 18 characters long with leading zeros.

[Declamatory]

Hi Folks,

I am creating a file for import to a client's system. One of the values is an amount. The cell has to be 18 characters long with no decimal points (the pence or cents will be the last two characters). If the amount in Sheet1 cell A1 was £19.99 then the cell B2 in sheet 2 would look like 000000000000001999.

How can I get the leading zeros in front of the amount bearing in mind that the amount could be anything from £0.00 to £10,000,000,000.00?

Does anyone have any ideas?

Thanks,

 

[FormR]

Hi, if you're looking for a formula method to return the new formatted value then here is one option:

Excel 2013/2016
	A	B
1	1	000000000000000100
2	1.5	000000000000000150
3	19.99	000000000000001999
4	10,000,000,000.00	000001000000000000
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(TEXT(A1,"0000000000000000.00"),".","")

 

[Declamatory]

That's spot on!

Thanks FormR. Much appreciated.