# Filling in cells with zero's

#### alexv

##### New Member
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!

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Use the custom format and make the format 000000000 or however many digits you need.

5555 becomes 000005555

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.

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

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.

Can you just multiply by 100.

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.

Maybe:

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

Try

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

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

Thanks for all the help!

Replies
1
Views
101
Replies
8
Views
138
Replies
6
Views
328
Replies
5
Views
308
Replies
2
Views
426

1,196,073
Messages
6,013,265
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back