Filling in cells with zero's

[alexv]

I need to create an excel file that I will save as a text file. The problem I am having is filling in cells with zero's when the cell value should be a certain number of characters long and the text value can vary. When I save the file as a text file, I don't want to lose the zero's. Help!

 

[Cberry]

Use the custom format and make the format 000000000 or however many digits you need.

5555 becomes 000005555

 

[alexv]

There is one more issue. If I need to join two cells, each with their own defined number of characters, I will lose the zero's when I join the two items.

 

[NBVC]

=TEXT(A1,"0000000000") will create a text string with the zeroes attached....then you can concatenate the strings without losing the zeroes.

 

[alexv]

Great, I think that's what I need. One other item I need for this file...I have a currency amount in a cell, and need to present that amount in a different cell without the decimals. ex, 1225.41 should be listed as 122541.

 

[NBVC]

Can you just multiply by 100.

 

[alexv]

Fabulous, didn't think of that.
One last thing, I would like to apply the cell value length to a cell containing text. Example, my cell says ABC Co. and I need the cell value to be 20 characters long, with any non text characters up to 20 filled in as blanks. So the cell should read ABC Co.(7 characters and 13 blanks next to it) so that if I do LEN formula on it, I will get 20, instead of 7. I hope this makes sense.

 

[NBVC]

Maybe:

=A1&REPT(" ",20-LEN(A1)) where A1 house the original string.

 

[barry houdini]

Try

=LEFT(A1&REPT(" ",20),20)

which wil increase 7 characters to 20 (with added spaces) or reduce +20 characters to 20

 

[alexv]

Thanks for all the help!