| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Leading zeroes in ZIP field | Excel Discussion (Misc queries) | |||
| excel .txt to .cvs, lose trailing zeroes in numeric field | Excel Discussion (Misc queries) | |||
| How do I format currency field in Excel 2000 w/o 2 extra zeroes? | Excel Discussion (Misc queries) | |||
| FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) | |||
| How do I format data cells in Excel to keep leading zeroes? | Setting up and Configuration of Excel | |||
Hybrid Mode
