Create display masks for synthetic identifiers and email addresses

Display masking is not deletion or anonymization

Use synthetic examples here. Keep identifiers as text so leading zeros and long digit strings are not lost before masking.

The example removes ordinary spaces and hyphens, masks all but the last four characters for longer identifiers, and masks the entire value when four or fewer characters remain.

The original value still exists in the source cells and may exist in history, exports and backups. Sharing the workbook still shares those source values. Hiding a column or displaying stars is not secure redaction.

A displayed last-four value or partially hidden email can remain identifying. These formulas do not establish PCI DSS or GDPR compliance, and do not replace access controls, appropriate storage or a reviewed data-release process.

Mask a synthetic text identifier

Returns an empty result for empty input. For four or fewer characters it reveals none; for longer strings it keeps the final four.

=IF(COUNTBLANK(A2)=1,"",LET(s,SUBSTITUTE(SUBSTITUTE(TRIM(A2&""),"-","")," ",""),IF(LEN(s)<=4,REPT("*",LEN(s)),REPT("*",LEN(s)-4)&RIGHT(s,4))))

Partially mask a simple email display

For a simple single-@ address, fully masks local parts of one or two characters, otherwise retains the first two. The domain remains visible. This is not email validation or anonymization.

=IFERROR(LET(localPart,TEXTBEFORE(B2,"@"),domainPart,TEXTAFTER(B2,"@"),IF(LEN(localPart)<=2,REPT("*",LEN(localPart)),LEFT(localPart,2)&REPT("*",LEN(localPart)-2))&"@"&domainPart),"Invalid email")

Further reading

• Microsoft Support — REPT function
• PCI SSC — Masking versus truncation