Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Partially Blocking Social Security Numbers.

Partially Blocking Social Security Numbers

by Allen Wyatt
(last updated January 14, 2017)

If you have a worksheet that includes Social Security Numbers in it, you may be looking for a way to protect the numbers by only displaying the last four digits. So, instead of displaying 278-53-6128, you would only want to display ***-**-6128.

The way to accomplish this depends, in large part, on whether the Social Security Number is stored in the cell as a number or as text. If the SSN is entered with its dashes (as in 278-53-6128), then Excel stores it as text. If the SSN is entered without dashes (as in 278536128), then Excel stores it as a number.

If the SSN is stored as a number, you may be tempted to create a custom format that hides the first part of the number. Unfortunately, there is no way to do this with a custom format. You could create a custom format that would hide all except the first digits, as in this manner:

000,,"-**-****"

As you can surmise from this example, custom formats don't allow you to mask out anything except the last portion of any value. Another drawback to this approach, however, is that Excel "rounds" the SSN, such that 278536128 is displayed as 279-**-****.

The best solution to displaying only the last part of a Social Security Number is to use a second column for the actual display. Instead of trying to format the number (or text) itself, it is best to use a formula that refers to the number and creates the desired result. For instance, if the SSN is in cell B7, then you would place the following formula in a different cell:

="***-****-" & RIGHT(B7,4)

This formula will work with any SSN, regardless of whether it is stored as a number or as text. The other big benefit to this approach is that it allows you to completely hide the original numbers. Even if you were able to use a custom format to hide the first portion of the number (which you can't), someone could still see the SSN in the Formula bar if the cell containing the number is selected.

Using the formula approach, however, allows you to hide the source column, or use sheet protection to hide the contents of the column. This is a big benefit if your goal is to really protect the Social Security Number from prying eyes.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3345) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Partially Blocking Social Security Numbers.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating a Numbered List

Numbered lists provide a 1-2-3 way of organizing your document. You can create numbered lists very easily using the ...

Discover More

Select All Changes by a Particular Reviewer

The Track Changes feature in Word allows you and other editors to easily collaborate on the development of a document. If you ...

Discover More

Inserting Cells in a Table

You can enlarge a table by adding cells where they are needed. Just pick where you want the cells inserted, then use the ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

MORE EXCELTIPS (MENU)

Copying Formatting

Excel provides a couple of different ways to copy formatting from one cell to another. Perhaps the easiest way is to use the ...

Discover More

Understanding Color and Conditional Formatting Codes

When you create custom cell formats, you can include codes that allow you to set the color of a cell and that specify the ...

Discover More

Too Many Cell Formats

The error message "too many cell formats" can be difficult to fix. This tip describes ways you can attempt to get rid of the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share