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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
How do I get it to work for the entire column?

John Bundy said:
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.
Click to expand...
 
The formula you have me assigned the value in cell A1 to 0. Is there another
function that may work better?
 
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.
 
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.
 
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.
 
That worked perfectly. Thanks for your help!

bj said:
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.
Click to expand...
 
Back
Top