A fixed field format needs fronting zeroes for each cells value.

[Guest]

I need to keep a fixed field width for a file that I am importing into
another program. The other program will only except a certain format. Each
field needs to have a specific number of characters and if the values are
less than that number we need to right justify and zero fill. Can we have
excel recognize the number values and zero fill, on the front end, if there
are not enough characters?

 

[Guest]

If i understand you right, you want your numbers a certain amount of digits
long, and if they are not, then add 0's until they are. If that is right you
can use this method
=TEXT(A1,"00000")

select the cell with the number, then put a 0 for the number of digits you
need, the above has 5 so the result of the number 124 would now be 00124.

 

[Guest]

That is exactly what I am looking for. Thanks for your help. You're swell!

 

[Guest]

How do I get it to work for the entire column?

If i understand you right, you want your numbers a certain amount of digits
long, and if they are not, then add 0's until they are. If that is right you
can use this method
=TEXT(A1,"00000")

select the cell with the number, then put a 0 for the number of digits you
need, the above has 5 so the result of the number 124 would now be 00124.

 

[Guest]

The formula you have me assigned the value in cell A1 to 0. Is there another
function that may work better?

 

[Guest]

Not sure what you mean by "you have me assigned the value in cell A1 to 0" I
was just saying for my formula, if the number you need changed is in cell A1,
put that formula in another like B1, then just drag it or copy and paste it
to the bottom of the column.

 

[Guest]

Instead of assigning the preceeding characters to zero, it is changing the
value of the number to zero. For example the number 123 would now have a
value of zero instead of 00123.

 

[Guest]

another way is to select the column
format-cell-number-custom and enter 00000 or what ever number of figures you
need.
this keeps it as a number and does not need another column.

 

[Guest]

That worked perfectly. Thanks for your help!

another way is to select the column
format-cell-number-custom and enter 00000 or what ever number of figures you
need.
this keeps it as a number and does not need another column.