Formatting Issues

How to Display Only the Last Four SSN Digits in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to know if it is possible to use an Excel custom number format to hide the first five digits of a Social Security Number (SSN) and display only the last four digits in the exact same cell.

Product: Excel
Device & OS: not provided
Scenario: Formatting and masking sensitive personal information like Social Security Numbers for safe reporting or data sharing.
Observed behavior: Users attempt to create a custom format code, but find that custom formatting cannot dynamically extract and display only the last four digits while retaining the full number in the same cell.

Before you start

Before modifying Social Security Numbers, understand that custom formatting only changes the visual appearance, not the underlying data. Anyone clicking on the cell can still see the full number in the formula bar.

Solution 1Recommended

Use the RIGHT Formula and Hide the Original Column

Since custom formats cannot reliably extract partial cell values, the most secure and effective method is to use a formula in a separate column to extract the last four digits.

Excel custom format codes apply to the entire numerical value and cannot drop specific digits from a sequence. To securely display only the last four digits, use a text extraction function.

Insert a Helper Column

Insert a new, blank column immediately next to the column containing your full Social Security Numbers.

Apply the RIGHT Formula

In the first empty cell of the new column, type the formula =RIGHT(A1,4) (assuming your first SSN is in cell A1) and press Enter. This extracts the last 4 characters from the left.

Copy the Formula Down

Click the cell with the formula, grab the small square at the bottom-right corner (fill handle), and drag it down to apply the formula to all relevant rows.

Hide the Original SSN Column

Right-click the letter header of the original SSN column (e.g., Column A) and select 'Hide' from the context menu to remove the sensitive data from view.

Security Tip for Sharing: Hiding columns does not permanently secure the data, as it can be easily unhidden. If you are sharing this file externally, copy the formula results, paste them as 'Values' in a new column, and permanently delete the original SSN column.

Solution 2

Create a Masked SSN Format with Asterisks

If you want to display the last four digits but also show asterisks indicating the hidden portion (e.g., ***-**-1234), combine a text string with the RIGHT function.

Data Privacy and Formatting

Manage and Mask Data Efficiently in WPS Spreadsheet

WPS Office provides robust text and data manipulation functions, allowing you to easily extract, mask, and secure sensitive information like SSNs. It offers an intuitive interface that makes formula application faster and simpler.

1. Open Your Data: Launch WPS Spreadsheet and open the file containing the SSN data you need to mask.
2. Enter the Extraction Formula: Click on a cell adjacent to your data, enter =RIGHT(A1,4), and press Enter to pull only the final four digits.
3. Convert to Values for Security: Copy the newly generated digits, right-click, choose 'Paste Special' > 'Values', and then delete the original column containing full SSNs for maximum privacy.

Fully compatible with Microsoft Excel (.xlsx and .xls) formats.Comprehensive support for text functions like RIGHT, LEFT, and CONCATENATE to handle data masking.Lightweight software with fast startup and low system resource consumption.Free and highly accessible for handling daily spreadsheet tasks securely.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use a custom number format like '***-**-0000' to mask the SSN?

Custom number formats only alter the visual display of the number (such as adding commas or dashes) but cannot selectively hide or discard existing digits from the cell's underlying value. The full number will remain exposed in the formula bar.

Is it secure to just hide the SSN column before emailing the Excel file?

No, simply hiding a column is easily reversible. Anyone who receives the file can unhide the column and view the sensitive data. For security, you must copy the extracted four digits, paste them as 'Values', and completely delete the original SSN column.

What if my SSNs are stored as text and already include dashes?

The RIGHT function works perfectly on text values. If your SSN is formatted as '123-45-6789', the formula =RIGHT(A1,4) will still correctly extract the '6789' part, ignoring the dashes.

Document & File Issues