Privacy Redaction: Using LEFT and REPT to mask sensitive data (and why it’s not “True” encryption).

Data redaction in Excel masks sensitive fields—such as Social Security numbers, bank routing details, credit card numbers, and executive salaries—by replacing specific characters with asterisks or hashes. While combining string functions like LEFT, RIGHT, and REPT creates a clean visual mask for reports and dashboards, it does not encrypt or secure the underlying data. Anyone with access to the workbook can still expose the raw information through formula references, worksheet unhiding, or file extraction.

Quick Answer

To mask all but the last four digits of an account or identification number, use:

=REPT("*", LEN(A2) - 4) & RIGHT(A2, 4)

For fixed 9-digit values like SSNs where you want to keep standard formatting:

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

Note: To truly protect the data, you must copy the masked results and use Paste Special > Values over the source, then delete the original unmasked column before sharing.

---

Constructing Dynamic Data Masks

A robust redaction formula must adapt dynamically to varying string lengths while preserving standard end-user verification identifiers (such as the final four digits).

Formula Pattern 1: Dynamic Masking by String Length

When masking employee ID strings, bank accounts, or customer IDs that have varying character lengths:

=REPT("*", MAX(0, LEN(A2) - 4)) & RIGHT(A2, 4)

• LEN(A2) - 4 calculates how many characters must be concealed.
• MAX(0, ...) protects against short entries (strings under 4 characters) so REPT does not throw a #VALUE! error from a negative repeat count.
• RIGHT(A2, 4) preserves the last four characters for human validation.

Formula Pattern 2: Center-String Redaction (Showing First and Last Digits)

For credit cards or national identifiers where both the starting prefix and ending suffix must remain visible for audit checks:

=LEFT(A2, 2) & REPT("*", MAX(0, LEN(A2) - 6)) & RIGHT(A2, 4)

If cell A2 contains "4111222233334444", this returns "41**********4444". If you are validating credit card strings prior to masking, refer to The “Check-Digit” Error: Using Excel to validate credit card or ISBN numbers via formula..

---

Why Formula Redaction Is Not “True” Encryption

A common corporate compliance error is assuming that a masked cell is secure. Formula-based masking is purely cosmetic. It creates three major data leakage risks:

[Source Data: Column A]  ----Formula Display----> [Masked Output: Column B]
       |                                                    |
       v                                                    v
  Still readable in grid!                              Appears as:
  Visible in Formula Bar                               ***-**-1234
  Visible in XML backend

1. Precedent Cell Exposure: If Column B masks Column A, Column A still resides in the workbook. Simply hiding Column A or protecting the worksheet with a password offers minimal protection. Excel sheet passwords can be cracked or bypassed in seconds using basic scripts or third-party tools.
2. Formula Auditing Traces: If an unauthorized user clicks on the masked cell, the formula bar instantly reveals the source cell coordinate (=...A2...). Running Trace Precedents will draw a visual arrow directly to the unencrypted data.
3. The .xlsx ZIP Archive Vulnerability: An .xlsx workbook is simply a zipped package of XML files. If an external party renames the file extension to .zip and opens xl/worksheets/sheet1.xml in a text editor, every raw, unmasked value from the source column is fully readable in cleartext, regardless of sheet protection.

---

How to Secure Redacted Data Before Distribution

To safely share a workbook containing redacted information, you must permanently remove the underlying source data:

1. Apply your masking formula in an adjacent column.
2. Select the entire masked column and press Ctrl + C.
3. Right-click the destination column and select Paste Special > Values (or press Ctrl + Alt + V then V).
4. Permanently Delete the original column containing the raw, unmasked PII.
5. Save the workbook as a new distribution copy.

Audit Check: Once completed, select a masked cell and look at the Formula Bar. It should display "*****1234", not =REPT(...).

If you are handling incoming IDs that suffer from dropped leading zeros or scientific notation before you apply masking, review SSN Formatting: Why leading zeros disappear and how to fix “Scientific Notation” in IDs.. For permission structures on locked distribution sheets, see #REF! in Protected Sheets: Missing Permissions for formula updates.