Challenge Scenario
To protect customer privacy and comply with data protection regulations, organizations must never expose full 16-digit credit card numbers on invoices, receipts, or customer service screens.
Security best practices require replacing the first 12 digits with fixed mask strings while retaining the last 4 digits for customer reference and transaction verification.
As a Data Security Assistant, your tasks are:
- Masked Card (Column C): Format the card into
"****-****-****-" combined with the last 4 digits using the RIGHT text function (="****-****-****-" & RIGHT(B2, 4)).
- Card Status (Column D): Check whether the card number has exactly 16 characters using
LEN(B2). If length equals 16, assign "VALID"; otherwise assign "INVALID".
- Total Customers (Cell C7): Count total customer records using
COUNTA(B2:B5).
- Valid 16-Digit Cards Count (Cell D8): Count how many cards are labeled
"VALID" using COUNTIF(D2:D5, "VALID").
Your Sheet Layout
Understanding how the customer payment security sheet is organized:
- Customer Accounts (Columns A & B): Shows Customer Name and raw unmasked card number strings.
- Sanitation & Validation (Columns C & D): Slices the last 4 digits, applies the asterisk security mask, and verifies 16-digit compliance.
- Security Audit Summary (Rows 7 & 8): Summarizes total customer profiles and counts verified valid cards.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Customer Name, Card Number |
Customer Records |
Raw customer credit card strings |
Original card data to sanitize. |
C2:C5 |
Masked Card |
Masking |
="****-****-****-" & RIGHT(B2, 4) |
Hides the first 12 digits and shows the last 4. |
D2:D5 |
Card Status |
Validation |
=IF(LEN(B2) = 16, "VALID", "INVALID") |
Checks if card number is exactly 16 digits. |
C7 |
TOTAL CUSTOMERS |
Security Audit |
=COUNTA(B2:B5) |
Counts total customer rows (4). |
D8 |
VALID 16-DIGIT CARDS |
Security Audit |
=COUNTIF(D2:D5, "VALID") |
Counts how many cards have 16 valid digits (3). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column C (C2:C5), mask card numbers using ="****-****-****-" & RIGHT(B2, 4).
2
In Column D (D2:D5), assign Card Status using =IF(LEN(B2)=16, "VALID", "INVALID").
3
In cell C7, count total customers using COUNTA(B2:B5).
4
In cell D8, count valid 16-digit cards using COUNTIF(D2:D5, "VALID").
Solution & Formula Breakdown
Let's examine how text masking and string length validation work step by step:
1. Masking Digits with RIGHT and & (C2:C5)
="****-****-****-" & RIGHT(B2, 4)
The RIGHT(text, num_chars) function extracts the final 4 characters from the card string:
- Alice Cooper (
4532890123456789): RIGHT extracts "6789" → "****-****-****-6789"
- Bob Marley (
5421987654321098): RIGHT extracts "1098" → "****-****-****-1098"
- Charlie Brown (
378282246310005): RIGHT extracts "0005" → "****-****-****-0005"
- Diana Ross (
4026007123459912): RIGHT extracts "9912" → "****-****-****-9912"
2. Validating Character Length with LEN (D2:D5)
=IF(LEN(B2) = 16, "VALID", "INVALID")
Checks character count: Charlie Brown's card has only 15 digits (LEN = 15), so it is flagged as "INVALID". All other cards receive "VALID".
3. Security Summary Rollups (C7 & D8)
- Total Customers (Cell C7):
=COUNTA(B2:B5) returns 4.
- Valid 16-Digit Cards (Cell D8):
=COUNTIF(D2:D5, "VALID") returns 3.
Combining text constants ("****-") with string functions like RIGHT using the ampersand (&) is the standard method for masking sensitive data (passwords, social security numbers, bank accounts) in spreadsheets!