Display numbers as Social Security numbers

What do you want to do?

• Display Social Security numbers in full

• Display only the last few digits of Social Security numbers

Display Social Security numbers in full

1. Select the cell or range of cells that you want to format.

   See how to select a cell or range of cells.

2. On the Home tab, click the Dialog Box Launcher next to Number.
   
   
   
   Or, select Number Format > More Number Formats.

3. In the Category box, select Special.

4. In the Type list, choose Social Security Number.

If you're in another location that uses Social Security numbers in a different format or a different identification number, choose your Locale and Language. Not all languages have an option, but some do.

Top of Page

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.

The following procedure uses example data to show how you can display only the last four numbers of a Social Security number. After you copy the formula to your worksheet, you can adjust it to display your own Social Security numbers in a similar manner.

1. Create a blank workbook or worksheet.

2. In this Help article, select the following example data without the row and column headers.

How to select example data

1. Click in front of the text in cell A1, and then drag the pointer across the cells to select all the text.

2. To copy the selected data, press CTRL+C.

3. In the worksheet, select cell A1.

4. To paste the copied data, press CTRL+V.

5. To switch between viewing the result and viewing the formula that returns the result, on the Formulas tab, in the Formula Auditing group, click Show Formulas.

   Keyboard shortcut - You can also press CTRL+` (grave accent).

   Important: 

   • 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, such as Microsoft Office Access), and then use only the last four digits of the numbers in your workbooks.

   • For more information about these functions, see CONCATENATE function, and RIGHT, RIGHTB functions.

   Notes: 

   • To prevent other people from viewing the entire Social Security number, you can first hide the column that contains that number (column B in the example data), and then protect the worksheet so that unauthorized users cannot unhide the data.

   • For more information, see Hide or display rows and columns and Protect worksheet or workbook elements.

Top of Page