Adding Zeros as placeholders

  • Thread starter Thread starter Seth
  • Start date Start date
S

Seth

Is there a formula to add zeros (or any character) to a
cell with a fixed width?
I am currently using a formula, =A1&REPT(" ",20-LEN(A1)),
which will extend the character length of a cell to 20,
this works for items that are left justified and entries
that contain fewer than 20 characters just have spaces to
fill up the remaining charaters.
What I am looking for is a way to add characters to the
front of an entry and still enforce the fixed length.
Example:
A cell contains 123456789
I would like the cell to have a fixed length of 20
chracters, and the "missing" characters are filled with
zeros placed in front of the entry. To look like this.
00000000000123456789
I am working with entries between 6 and 10 characters in
length.

Thank you in adavance for your time.
 
=REPT("0",20-LEN(A1))&A1

This is based on your formula, just switched around. Is that the whole
story?
 
Seth, how about a custom format? Select the cells and Format > Cells >
Number > Custom. In the Type: field enter 00000000000000000000 and click OK.
 
If you want one formula to handle both types of data, you can check for
a number in cell A1:

=IF(ISNUMBER(A1),REPT(0,20-LEN(A1))&A1,A1&REPT(" ",20-LEN(A1)))
 
Hi Frank,

Truth to tell, I had my doubts, too, knowing that Excel only allows so many
significant digits. However, Seth did say that most of his entries were in
the range of 6-10 digits, and in my light testing, the format delivered the
desired result every time. So I thought I might suggest it as an alternative
to the formula-based solutions that other respondents provided.
 
Back
Top