Formatting Cells using Excel: Automatic input of 2 Zeros

Asked By Jennifer Younghusband 200 points N/A Posted on - 06/05/2011

Hi there TechyV experts.

I am currently working in a financial institution which requires me to enter account numbers in Excel. All was fine until I encountered an issue which I cannot get out with.

This entails an account number with two zeros at the beginning. Of course, I cannot specify the exact account number, so I apologize for that. I tried typing the account number in Word, Notepad, Wordpad then pasted it in Excel, but just the same, it automatically omitted the two zeros.

I tried to look in formatting the Excel, but I cannot seem to see any option that would retain the two zeros at the beginning of the account number.

Hope somebody can help please.

• Status: Open
• Question Views: 1779
• Answer Count: 2
• Vote Up 20 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Berenk guin

Go To Solution

Best Answer

Best Answer

Answered By Berenk guin 0 points N/A #112654

Formatting Cells using Excel: Automatic input of 2 Zeros

This thing has to be done by the formatting. I think you have missed that.

• Just right click a cell and select the Format Cell from the menu that appears. Then you will get the Format Cells dialog box. 
• Click the Number tab and select the Custom from the Category you see in the left side.

So from here you can give a format for your account number.

If your account number is like this 00123456 – 8 digits number with two zeros at the beginning:

Type this in the text box labeled Type: 00######

This means it has two zeros at the beginning and 6 numbers after that.

When you apply this format to a cell, you do not need to type those two zeros at the beginning. It will automatically add to the number itself when you type. Just try it out. You will find this is very easy.

If you do not like this kind of automatic formatting, you can just ignore this and make the cell as a text format and here you have to type the whole thing with the zeros. Just go to the Format Cell dialog box by following the above procedure and from the Category select Text. But I prefer you to do the first method. It will make your tasks easier.

If you want to add letters at the end or commas, dashes or any kind of mask like thing you can add it here.

For example if you give this kind of custom formatting 000-00-0000 for a cell, it will give you 000-00-0045 when you type 45 in the cell.

Don’t you think it makes work easier rather that typing the whole thing with dashes and other stuff and wasting your time?

About Berenk guin

Questions
0

Answers
14

Best Answers
9

• Vote Up 0 Vote Down

• Posted on - 06/05/2011
• Question Category: MS Excel

Answered By LisaRoberts 0 points N/A #112656

Formatting Cells using Excel: Automatic input of 2 Zeros

To have leading zeros in the number you entered, you have to customize those cells.

These are the steps that you have to follow:

• Select the cell range that you want to have zeros before the number.
• Click Home tab.
• Click on the down arrow in Number Format. Click on More Number Formats.
• Select the Custom option in Number Tab.
• Then delete the word ‘General’ under Type.
• Now enter the following format. 00## and press Enter key or click on Ok. In the cell type the number you want as 25. It will appear as 0025.

Your problem is solved.

About LisaRoberts

Questions
0

Answers
5

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 07/28/2011
• Question Category: MS Excel