Custom formatted cells. Can't concatenate or save format in TXT file

[Declamatory]

Hi Folks,

I have some data in a workbook (sheet 1). In sheet 2 I am trying to pull together bits of the data into a format that a client can use to upload the information to their systems.

Where I have the price of something in sheet 1 £19.99 in sheet 2 this information needs to be 000000000019990000. I've done this using conditional formatting based on the number of figures in the price (changes from £1, £10, £100 etc.) using custom format to insert leading and trailing zeros.

The various fields then need to be saved in a txt file for upload with no spaces between the various figures and info I've collated in sheet 2.

The problem I have is that when I try to save in the txt file the leading and trailing zeros disappear. It's the same if I try to concatenate the data into one cell so no spaces appear in the text file. The custom format disappears.

Does anyone have any idea how I can keep the custom format when concatenating?

Thanks,

 

[wideboydixon]

Did you try setting the cell number format to "@" (i.e. text)?

WBD

 

[Declamatory]

Thanks, I have tried it but it doesn't seem to have any affect. Format was "00000000000"#"0000" and is now"00000000000"@"0000".

 

[wideboydixon]

What about using TEXT() formula:

Book1
	A	B
1	£19.99	000000000019990000
Welcome

Cell Formulas
Range	Formula
B1	=RIGHT("0000000000"&A1*100&"0000",18)

WBD

 

[Declamatory]

I think that's it. I'll have do do some nested IFs for the prices changes but that's no bother.