Data Entry with Leading Zeros
A leading zero (00233) is any 0 digit that is placed before the first nonzero digit in a number string in positional notation. Examples of these are social security numbers, phone numbers, credit card numbers, product codes, account numbers, or postal codes.
Learning Outcomes
By the end of this lesson, you should be able to:
- identify different types of text entries
- identify different types of numeric entries
- differentiate a text entry from a numeric entry
- manage data entries with leading zeros
Types of Entries in a Cell
By default, Excel recognizes two types of entries – text entry and numeric entry. How do we, by inspection, recognize a text entry or a numeric entry during data entry?
A text entry by default is left-aligned. Some examples of text entries you can type in a cell are:
- telephone number – 026 765 7171
- account number – 1131120021508
- car number – GW 3665 T
- name – Adam
- country code – 00233
- the telephone network code – 050
In figure 1 the text entries under Student Id, gender, status and specialty are left-aligned. The numeric entries in date, CA, final eams and total scores are right-aligned. With this arrangement, you should be able to confirm during data entry whether Excel indeed recognizes your entries as text or numeric.
By default, a numeric entry is right-aligned in a cell. Examples of numeric entries that you can enter in a cell are:
- amount – $1200
- score of an exam – 65.5
- date – May 1, 2020
- time – 01:30 pm
If your task is to do calculations with your entries, then ensure that Excel recognizes your entries as numeric entries.
How to Retain Leading Zeros
Let’s demonstrate how you can introduce and retain leading zeros of numeric entries.
1. Select an empty cell, e.g., A1.
2. Select Home tab.
3. Go to the Number group.
4. Click the Dialog Box launcher.
5. Select Text in the General Tab.
6. Click OK.
7. Type 01 in cell A1.
8. Press Enter.
9. Click A1 again.
Note that Excel displays a small green triangle in the top-left corner of the cell A1 (figure 3). This indicates that something is wrong with the cell contents.
To fix the error indicator in the cell contents by doing the following:
10. Click this warning sign:
12. Click Ignore Error (figure 4).
Note
Creating Custom Number Format
Custom number formats control how you can display numbers, dates, times, fractions, percentages, and other numeric values. Using custom formats, you can format dates to show month names only, format large numbers in millions or thousands, and display negative numbers in red color.
To apply a custom number format to leading zeros perform these tasks:
1. Select a cell or range of cells where you want to display leading zeros
2. Press Ctrl+1 to open the Format Cells dialog box.
3. Select Custom, under Category.
4. Type a format code in the Type box.
In most cases, you will need a format code consisting of 0 placeholders, like 00. The number of zeros in the format code corresponds to the total number of digits you want to show in a cell.
5. Click OK.
Example
Use leading zeros to create a fixed-length 5-digit number system.
Solution
An animation of how to use leading zeros to create a 5-digit number is demonstrated in figure 5:
Fig 5: Creating a fixed-length number with leading zeros
Alternatively, the task for creating a 5-digit number using leading zeros is explained in the following steps:
1. Select a cell or range of cells where you want to display leading zeros.
2. Press Ctrl+1 to open the Format Cells dialog box. Or click the dialog box launcher for Number group.
3. Select Custom, under Category.
4. Type a format code 00000 in the Type box (figure 6).
5. Click OK.
By using Excel custom numbers formats, you can add leading zeros to create fixed-length numbers (figure 6), and variable-length numbers.
Special Formats
Excel has a few predefined Special formats for postal codes, telephone numbers and social security numbers'(figure 7) . This and other custom number format are covered extensively in Data Entry 2.
Note that custom formats can only be applied to numeric data (numbers and dates) where the result is also a number or date and not text.
Task 1
1. Use Excel to create the file in Figure 1
2. Replace student Status with Telephone
3. Use arbitrary data to complete the entries starting with your profile. Do not include Total column
4. Save your work with your IUCG ID on the desktop
▣▣▣
Leave A Reply
You must be logged in to post a comment.
2 Comments
Sir please the problem I am facing is the login method. After I finish the login process by using my gmail. A blue page appears with a (W) sign on it and if I try to put in the same gmail and password, then it will prompt me that I have two attempt remaining. That is my problem. Thank you sir.
I’ve learnt a lot today , can’t wait for tomorrow’s lecture.