How to Mask Digits in WPS Office Spreadsheets with a Helper Formula
Question details
The user needs to display sensitive numeric values in WPS Office Spreadsheets with the middle digits masked, for example 1234567 as 12***67, while keeping the original values in a separate column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with sensitive numbers in a WPS Spreadsheets workbook and wants to mask part of each number for display without deleting or altering the original data.
- Observed behavior
- The goal is to show a masked text value in a helper column, such as 12***67, while the original value remains available in another column.
Open your WPS Office Spreadsheets workbook and make sure the original numbers are in a separate column from where you want the masked result. Identify the first data row so you can enter and fill the helper formula correctly.
Create a Helper Formula to Mask Digits
Use this method when you want a live masked version that updates if the original value changes.
This formula takes the first two characters and the last two characters from the original cell, placing three asterisks between them. The result is a text string, so you can display the mask without changing the original value.
Click the first cell where you want the masked result to appear, such as B2, next to the original value in A2.
Type =LEFT(A2,2)&"***"&RIGHT(A2,2) and press Enter.
Select the cell with the formula, then drag the fill handle down to apply it to the remaining rows.
Keep Masked Results as Static Values with Paste Values
Use this method when you no longer need the formula to update and want to keep only the masked text.
Mask Sensitive Numbers in WPS Spreadsheets with a Helper Formula
Use WPS Office Spreadsheets to show a masked version of sensitive numbers while keeping the original data untouched in a separate column. The formula works in .xlsx workbooks and is easy to apply without advanced skills.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the sensitive numbers.
- 2. Create a helper column: Click the first cell in an empty column next to the original values, for example B2.
- 3. Enter the masking formula: Type =LEFT(A2,2)&"***"&RIGHT(A2,2) and press Enter.
- 4. Fill the formula down: Drag the fill handle down to apply the formula to the remaining cells.
- 5. Save the workbook: Press Ctrl+S to save the masked display and the original data.
Frequently Asked Questions
Will the formula work if my original value is stored as a number?
Yes. The formula returns a text string such as 12***67, but it does not change the original numeric cell. Use Paste Values if you need a static result.
Can I show more than two digits at the beginning or end?
Yes. Change LEFT(A2,2) and RIGHT(A2,2) to the number of characters you want to keep, and adjust the asterisk string between them.
Does masking digits protect confidential data?
No. This is a visual display method only. The original column still contains the full value, so restrict access or use encryption for real data protection.
How do I remove the formula but keep the masked text?
Copy the masked cells, right-click the target area, and choose Paste Values. In WPS Office you can also use Ctrl+Shift+V.
