Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

You can format a number as a Social Security number. For example, you can format a 9-digit number, such as 555501234, as 555-50-1234.

What do you want to do?

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.

    Excel Ribbon Image

    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

2

3

4

A

B

Type

Data

Social Security Number

555-50-1234

Formula

Description (Result)

=CONCATENATE("000-00-", RIGHT(B2,4))

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 (000-00-1234)

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

  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: 

Top of Page

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×