Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a number have a certain number of characters.
Hi All I have a small problem. I column A I have a row of cells , some of which have numbers , some of which are empty. The numbers usually have 12 or 13 chars. Some have fewer than 12 chars. I need to make these up to 12 chars by placing zeros at the start of each. Cells with numbers already at 12 or 13 chars , or which are blank should remain unaltered. Can someone help with a formula to achieve this? Thanks. Grateful for any advice. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a number have a certain number of characters.
On Thu, 15 Sep 2011 00:45:51 +0100, Colin Hayes wrote:
Hi All I have a small problem. I column A I have a row of cells , some of which have numbers , some of which are empty. The numbers usually have 12 or 13 chars. Some have fewer than 12 chars. I need to make these up to 12 chars by placing zeros at the start of each. Cells with numbers already at 12 or 13 chars , or which are blank should remain unaltered. Can someone help with a formula to achieve this? Thanks. Grateful for any advice. You can custom format the cells: Type: 000000000000 (That's 12 0's) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a number have a certain number of characters.
Hi Colin
Select your cells, right click, format cell and select Custom the type 12 "0"zeros e.g. 000000000000 HTH Cimjet "Colin Hayes" wrote in message ... Hi All I have a small problem. I column A I have a row of cells , some of which have numbers , some of which are empty. The numbers usually have 12 or 13 chars. Some have fewer than 12 chars. I need to make these up to 12 chars by placing zeros at the start of each. Cells with numbers already at 12 or 13 chars , or which are blank should remain unaltered. Can someone help with a formula to achieve this? Thanks. Grateful for any advice. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a number have a certain number of characters.
In article , Cimjet
writes Hi Colin Select your cells, right click, format cell and select Custom the type 12 "0"zeros e.g. 000000000000 HTH Cimjet Hi OK Thanks. I did try this , and it works fine on the screen. I save it as tab delimited. When I re-open the file the zeros have all disappeared again , and I'm back with my too-short numbers. How can I make the zeros stick? Thanks again. "Colin Hayes" wrote in message ... Hi All I have a small problem. I column A I have a row of cells , some of which have numbers , some of which are empty. The numbers usually have 12 or 13 chars. Some have fewer than 12 chars. I need to make these up to 12 chars by placing zeros at the start of each. Cells with numbers already at 12 or 13 chars , or which are blank should remain unaltered. Can someone help with a formula to achieve this? Thanks. Grateful for any advice. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a number have a certain number of characters.
Hi again
Start your custom code with a apostrophe. e.g. '000000000000 Excel will not accept zeros at the start of a number, the Apostrophe turn it in to text HTH Cimjet "Colin Hayes" wrote in message ... In article , Cimjet writes Hi Colin Select your cells, right click, format cell and select Custom the type 12 "0"zeros e.g. 000000000000 HTH Cimjet Hi OK Thanks. I did try this , and it works fine on the screen. I save it as tab delimited. When I re-open the file the zeros have all disappeared again , and I'm back with my too-short numbers. How can I make the zeros stick? Thanks again. "Colin Hayes" wrote in message ... Hi All I have a small problem. I column A I have a row of cells , some of which have numbers , some of which are empty. The numbers usually have 12 or 13 chars. Some have fewer than 12 chars. I need to make these up to 12 chars by placing zeros at the start of each. Cells with numbers already at 12 or 13 chars , or which are blank should remain unaltered. Can someone help with a formula to achieve this? Thanks. Grateful for any advice. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a number have a certain number of characters.
On 15/09/2011 01:18, Colin Hayes wrote:
In article , Cimjet writes Hi Colin Select your cells, right click, format cell and select Custom the type 12 "0"zeros e.g. 000000000000 HTH Cimjet Hi OK Thanks. I did try this , and it works fine on the screen. I save it as tab delimited. When I re-open the file the zeros have all disappeared again , and I'm back with my too-short numbers. How can I make the zeros stick? When you save as tab-delimited you are saving as a text file. That file will include the leading zeroes. The problem arises if you use Excel to open the text file using Excel's default options. If you want to import the data from the text file, tell Excel at the import stage that the relevant column is text, not General. David Biddulph "Colin Hayes" wrote in message ... Hi All I have a small problem. I column A I have a row of cells , some of which have numbers , some of which are empty. The numbers usually have 12 or 13 chars. Some have fewer than 12 chars. I need to make these up to 12 chars by placing zeros at the start of each. Cells with numbers already at 12 or 13 chars , or which are blank should remain unaltered. Can someone help with a formula to achieve this? Thanks. Grateful for any advice. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to make a number have a certain number of characters.
On 15 Set, 02:18, Colin Hayes wrote:
In article , Cimjet writes Hi Colin Select your cells, right click, format cell and select Custom the type 12 "0"zeros e.g. 000000000000 HTH Cimjet Hi OK Thanks. I did try this , and it works fine on the screen. I save it as tab delimited. When I re-open the file the zeros have all disappeared again , and I'm back with my too-short numbers. How can I make the zeros stick? Thanks again. "Colin Hayes" wrote in message ... Hi All I have a small problem. I column A I have a row of cells , some of which have numbers , some of which are empty. The numbers usually have 12 or 13 chars. Some have fewer than 12 chars. *I need to make these up to 12 chars by placing zeros at the start of each. Cells with numbers already at 12 or 13 chars , or which are blank should remain unaltered. Can someone help with a formula to achieve this? Thanks. Grateful for any advice.- Nascondi testo citato - Mostra testo citato - Hi Colin. Try: =TEXT(A1,"0000000000000") Regards Eliano |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help with having characters in the number | Excel Discussion (Misc queries) | |||
number of characters in each cell | Excel Discussion (Misc queries) | |||
Number of Characters | Excel Discussion (Misc queries) | |||
Sorting by Number of Characters | Excel Discussion (Misc queries) | |||
How to make a number round up/down to a set number | Excel Worksheet Functions |