Excel provides special number formats for postal codes, Social Security numbers, and phone numbers. If these don't meet your needs, you can create and apply your own custom number format.
Apply a predefined postal code format to numbers
-
Select the cell or range of cells that you want to format.
-
On the Home tab, next to General, in the Number Format box
, click the arrow, and then click More Number Formats. -
In the Format Cells dialog box, under Category, click Special.
-
In the Type list, click Zip Code or Zip Code + 4.
Note: These special number formats are available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for postal codes.
Create a custom postal code format
If the special postal code formats don't meet your needs, you can create a custom postal code format.
Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open.
-
Select the cell or range of cells that you want to format.
-
On the Home tab, next to General, in the Number Format box
, click the arrow, and then click More Number Formats. -
In the Format Cells dialog box, under Category, click Custom.
-
In the Type list, select the number format that you want to customize. The code used for the number format appears in the Type box above the Type list.
Tip: To customize the predefined Zip Code + 4 format, select 00000-0000 in the Type list. When you select a built-in number format in the Type list, Excel creates a copy of that number format that you can customize.
-
In the Type box, make the necessary changes to the selected number format.
Tip: If a range of cells contains both five-digit and nine-digit ZIP codes, you can enter [<=99999]00000;00000-0000 to display both types of ZIP Codes correctly in the worksheet.
Include leading characters in postal codes
You can format a cell or range of cells to display leading characters so that the postal code is preceded by enough characters to fill the cell's width. For example, you can use zeros or dashes to display a postal code as follows: 0000000 98052 or ------- 98052.
Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open.
-
Select the cell or range of cells that you want to format.
-
On the Home tab, next to Number in the Number Format box
, click the arrow, and then click More Number Formats. -
In the Format Cells dialog box, under Category, click Custom.
-
In the Type box, type *0 followed by the postal code format that you want to use.
Tip: For example, for a 5-digit postal code, type *0#####
Display Social Security numbers in full
-
Select the cell or range of cells that you want to format.
-
On the Home tab, next to Number in the Number Format box
, click the arrow, and then click More Number Formats. -
In the Format Cells dialog box, under Category, click Special.
-
In the Type list, click Social Security Number.
Note: This special number format is available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for Social Security numbers.
Display only the last few digits of Social Security numbers
For common security measures, you may want to display only the last few digits of a Social Security number and replace the rest of the digits with zeros or other characters. You can do this by using a formula that includes the CONCATENATE and RIGHT functions. This procedure assumes that the complete Social Security numbers are located in one column and that you have a second column where you will display only the last few digits of the Social Security numbers.
Important: To prevent other people from viewing an entire Social Security number, you can first hide the column that contains that number, and then protect the worksheet so that unauthorized users cannot unhide the data. As a best practice, you may want to avoid storing complete Social Security numbers in your workbooks. Instead, store the full Social Security numbers in a location that meets stringent security standards (for example, a database program), and then use only the last four digits of the numbers in your workbooks.
-
In an empty column, click in the first cell.
-
On the Formulas tab, click Insert Function, type TEXT, and then type CONCATENATE.
-
In the Formula Bar, edit the formula so that it looks like this:
=CONCATENATE("000-00-", RIGHT(<CELL1>:<CELLn>,4))
where <CELL1> is the first cell (for example, A1) and <CELLn> is the last cell (for example, A10) in the range of cells that contain the Social Security numbers.
Excel displays the "000-00-" text string instead of the first 5 digits of the Social Security number and combines it with the last four digits of the Social Security number (for example, 000-00-1234).
-
In the cell that contains the formula, drag the fill handle down the column to populate the remaining cells in the range.
Display numbers as a phone number
-
Select the cell or range of cells that you want to format.
-
On the Home tab, next to Number in the Number Format box
, click the arrow, and then click More Number Formats. -
In the Format Cells dialog box, under Category, click Special.
-
In the Type list, click Phone Number.
Note: This special number format is available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for phone numbers.
Apply a predefined postal code format to numbers
-
Select the cell or range of cells that you want to format.
-
On the Home tab, under Number, on the Number Format pop-up menu
, click Custom. -
In the Format Cells dialog box, under Category, click Special.
-
In the Type list, click Zip Code or Zip Code + 4.
Note: These special number formats are available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for postal codes.
Create a custom postal code format
If the special postal code formats don't meet your needs, you can create a custom postal code format.
Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open.
-
Select the cell or range of cells that you want to format.
-
On the Home tab, under Number, on the Number Format pop-up menu
, click Custom. -
In the Format Cells dialog box, under Category, click Custom.
-
In the Type list, select the number format that you want to customize. The code used for the number format appears in the Type box above the Type list.
Tip: To customize the predefined Zip Code + 4 format, select 00000-0000 in the Type list. When you select a built-in number format in the Type list, Excel creates a copy of that number format that you can customize.
-
In the Type box, make the necessary changes to the selected number format.
Tip: If a range of cells contains both five-digit and nine-digit ZIP codes, you can enter [<=99999]00000;00000-0000 to display both types of ZIP Codes correctly in the worksheet.
Include leading characters in postal codes
You can format a cell or range of cells to display leading characters so that the postal code is preceded by enough characters to fill the cell's width. For example, you can use zeros or dashes to display a postal code as follows: 0000000 98052 or ------- 98052.
Important: Custom number formats affect only the way a number is displayed and do not affect the underlying value of the number. Custom number formats are stored in the active workbook and are not available to new workbooks that you open.
-
Select the cell or range of cells that you want to format.
-
On the Home tab, under Number, on the Number Format pop-up menu
, click Custom. -
In the Format Cells dialog box, under Category, click Custom.
-
In the Type box, type *0 followed by the postal code format that you want to use.
Tip: For example, for a 5-digit postal code, type *0#####
Display Social Security numbers in full
-
Select the cell or range of cells that you want to format.
-
On the Home tab, under Number, on the Number Format pop-up menu
, click Custom. -
In the Format Cells dialog box, under Category, click Special.
-
In the Type list, click Social Security Number.
Note: This special number format is available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for Social Security numbers.
Display only the last few digits of Social Security numbers
For common security measures, you may want to display only the last few digits of a Social Security number and replace the rest of the digits with zeros or other characters. You can do this by using a formula that includes the CONCATENATE and RIGHT functions. This procedure assumes that the complete Social Security numbers are located in one column and that you have a second column where you will display only the last few digits of the Social Security numbers.
Important: To prevent other people from viewing an entire Social Security number, you can first hide the column that contains that number, and then protect the worksheet so that unauthorized users cannot unhide the data. As a best practice, you may want to avoid storing complete Social Security numbers in your workbooks. Instead, store the full Social Security numbers in a location that meets stringent security standards (for example, a database program), and then use only the last four digits of the numbers in your workbooks.
-
In an empty column, click in the first cell.
-
On the Formulas tab, under Function, click Insert, point to Text, and then click CONCATENATE.
-
In the Formula Bar, edit the formula so that it looks like this:
=CONCATENATE("000-00-", RIGHT(<CELL1>:<CELLn>,4))
where <CELL1> is the first cell (for example, A1) and <CELLn> is the last cell (for example, A10) in the range of cells that contain the Social Security numbers.
Excel displays the "000-00-" text string instead of the first 5 digits of the Social Security number and combines it with the last four digits of the Social Security number (for example, 000-00-1234).
-
In the cell that contains the formula, drag the fill handle down the column to populate the remaining cells in the range.
Display numbers as a phone number
-
Select the cell or range of cells that you want to format.
-
On the Home tab, under Number, on the Number Format pop-up menu
, click Custom. -
In the Format Cells dialog box, under Category, click Special.
-
In the Type list, click Phone Number.
Note: This special number format is available only if the Location (Language) is set to United States (English). Different locales provide different or no special number formats for phone numbers.
See also
Create and apply a custom number format
Display dates, times, currency, fractions, or percentages
No comments:
Post a Comment