Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
I need to fill cells 15 charecters in length with zeroes, the cells have
numbers in them already, ex. 45678 needs to read 000000000045678. The numers in the cells are not the same length, some are 4 charecters up to 7 charechters. Can this be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
I hope this may help;
Just open the cell's format, choose the "Number" tab, under the category choose "Custom", then in the "Type" box write the 15 zeros. "Type:" 000000000000000 this will do it i hope -- Keep the Hope "Mike B" wrote: I need to fill cells 15 charecters in length with zeroes, the cells have numbers in them already, ex. 45678 needs to read 000000000045678. The numers in the cells are not the same length, some are 4 charecters up to 7 charechters. Can this be done? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
This is fine, but if I try to use a function after this like concantonate the
zero's go away. Essentialy I wan't to do the same thing as Mike B. I have a list of numbers that are different lengths and I need them all to be front filled w/ zeros up to nine digits. I can do this by using a custom format for visual purposes, but I need to be able to concanonate another column into that column and this doesn't seem to work for that. ex. column1 coulmn2 10000 12345 use custom format gets me the following: column1 coulmn2 10000 000012345 That is good but now I need to push column1 and column 2 together w/o changing them. so I concantonated column1 and coulm 2 expecting to get: 10000000012345 but instead I get: 1000012345 It drops the front filled zeros. This is driving me crazy. "Mustafa" wrote: I hope this may help; Just open the cell's format, choose the "Number" tab, under the category choose "Custom", then in the "Type" box write the 15 zeros. "Type:" 000000000000000 this will do it i hope -- Keep the Hope "Mike B" wrote: I need to fill cells 15 charecters in length with zeroes, the cells have numbers in them already, ex. 45678 needs to read 000000000045678. The numers in the cells are not the same length, some are 4 charecters up to 7 charechters. Can this be done? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
The zeros you are losing are not really there.
They are a result of the custom format. =A1 & "0000" & B1 should do the trick. Gord Dibben MS Excel MVP On Wed, 11 Jun 2008 16:46:00 -0700, JR wrote: This is fine, but if I try to use a function after this like concantonate the zero's go away. Essentialy I wan't to do the same thing as Mike B. I have a list of numbers that are different lengths and I need them all to be front filled w/ zeros up to nine digits. I can do this by using a custom format for visual purposes, but I need to be able to concanonate another column into that column and this doesn't seem to work for that. ex. column1 coulmn2 10000 12345 use custom format gets me the following: column1 coulmn2 10000 000012345 That is good but now I need to push column1 and column 2 together w/o changing them. so I concantonated column1 and coulm 2 expecting to get: 10000000012345 but instead I get: 1000012345 It drops the front filled zeros. This is driving me crazy. "Mustafa" wrote: I hope this may help; Just open the cell's format, choose the "Number" tab, under the category choose "Custom", then in the "Type" box write the 15 zeros. "Type:" 000000000000000 this will do it i hope -- Keep the Hope "Mike B" wrote: I need to fill cells 15 charecters in length with zeroes, the cells have numbers in them already, ex. 45678 needs to read 000000000045678. The numers in the cells are not the same length, some are 4 charecters up to 7 charechters. Can this be done? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
You need to use a formula such as =RIGHT("000000000"&A1,9) to produce a 9
digit string with preceding zeros. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
I have another issue which is exactly related... but a little different twist.
I have a 14 character long number field that has to be parsed. Similar to what my colleague has experienced, I had to format the numbers to assure that there were a correct number of characters per field. Here is and example of the number: 03103104290000 When I parse this info I need to retain the exact number of characters in the string as follows (each number is now in a separate column): 03 103 10429 00 00 By formatting the columns with a custom number format I get the desired result... ON THE SCREEN. Now I need to add dashes in between each space so the numbers desired result would be: 03-103-10429-00-00 If you use the CONCANTONATE function, or &, to string them together using the following formula: =A1&"-"&A2&"-"&A3&"-"&A4&"-"&A5 the result is: 3-103-10429-0-0 So, like my friend here, this is driving me nuts. It gets to be a real pain when the 3rd column (5 digets) is preceeded by a "0"... then I only have 4 digits in the field. Any ideas on how to fix this?? "Mike B" wrote: I need to fill cells 15 charecters in length with zeroes, the cells have numbers in them already, ex. 45678 needs to read 000000000045678. The numers in the cells are not the same length, some are 4 charecters up to 7 charechters. Can this be done? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
Try using the TEXT function instead of concatenation...
=TEXT(A1,"00-000-00000-00-00") -- Rick (MVP - Excel) "gwaubrey" wrote in message ... I have another issue which is exactly related... but a little different twist. I have a 14 character long number field that has to be parsed. Similar to what my colleague has experienced, I had to format the numbers to assure that there were a correct number of characters per field. Here is and example of the number: 03103104290000 When I parse this info I need to retain the exact number of characters in the string as follows (each number is now in a separate column): 03 103 10429 00 00 By formatting the columns with a custom number format I get the desired result... ON THE SCREEN. Now I need to add dashes in between each space so the numbers desired result would be: 03-103-10429-00-00 If you use the CONCANTONATE function, or &, to string them together using the following formula: =A1&"-"&A2&"-"&A3&"-"&A4&"-"&A5 the result is: 3-103-10429-0-0 So, like my friend here, this is driving me nuts. It gets to be a real pain when the 3rd column (5 digets) is preceeded by a "0"... then I only have 4 digits in the field. Any ideas on how to fix this?? "Mike B" wrote: I need to fill cells 15 charecters in length with zeroes, the cells have numbers in them already, ex. 45678 needs to read 000000000045678. The numers in the cells are not the same length, some are 4 charecters up to 7 charechters. Can this be done? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO ZERO FILL A CELL THAT CONTAINS OTHER DATA EX. 000123?
Nice... works like a charm. I owe you one!!!
George "Rick Rothstein" wrote: Try using the TEXT function instead of concatenation... =TEXT(A1,"00-000-00000-00-00") -- Rick (MVP - Excel) "gwaubrey" wrote in message ... I have another issue which is exactly related... but a little different twist. I have a 14 character long number field that has to be parsed. Similar to what my colleague has experienced, I had to format the numbers to assure that there were a correct number of characters per field. Here is and example of the number: 03103104290000 When I parse this info I need to retain the exact number of characters in the string as follows (each number is now in a separate column): 03 103 10429 00 00 By formatting the columns with a custom number format I get the desired result... ON THE SCREEN. Now I need to add dashes in between each space so the numbers desired result would be: 03-103-10429-00-00 If you use the CONCANTONATE function, or &, to string them together using the following formula: =A1&"-"&A2&"-"&A3&"-"&A4&"-"&A5 the result is: 3-103-10429-0-0 So, like my friend here, this is driving me nuts. It gets to be a real pain when the 3rd column (5 digets) is preceeded by a "0"... then I only have 4 digits in the field. Any ideas on how to fix this?? "Mike B" wrote: I need to fill cells 15 charecters in length with zeroes, the cells have numbers in them already, ex. 45678 needs to read 000000000045678. The numers in the cells are not the same length, some are 4 charecters up to 7 charechters. Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Last cell of data in a column | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
data in cell not corresponding with entry | New Users to Excel | |||
Input Cell in One variable data table | Excel Worksheet Functions |