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

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?

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

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?

A fixed field format needs fronting zeroes for each cells valu
 

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?

A fixed field format needs fronting zeroes for each cells valu
 

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?

A fixed field format needs fronting zeroes for each cells valu
 

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?

A fixed field format needs fronting zeroes for each cells valu
 

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?

A fixed field format needs fronting zeroes for each cells valu
 

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.

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?

A fixed field format needs fronting zeroes for each cells valu
 

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.

"CommerceMary" wrote:

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.

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?

A fixed field format needs fronting zeroes for each cells valu
 

That worked perfectly. Thanks for your help!

"bj" wrote:

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.

"CommerceMary" wrote:

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.

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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

"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"CommerceMary" wrote:

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?