Challenge Scenario
When barcode scanners or warehouse inventory systems export product records, raw SKU strings often contain accidental leading and trailing whitespace, inconsistent lowercase letters, or irregular padding.
To standardize SKU codes for ERP database consistency, data administrators apply TRIM to remove stray spaces and UPPER to capitalize all letters uniformly.
As a Warehouse Master Data Specialist, your tasks are:
- Cleaned SKU (Column C): Strip extra whitespace and convert letters to uppercase using
=UPPER(TRIM(B2)).
- SKU Length (Column D): Measure the character length of the cleaned SKU using
LEN(C2).
- Total SKUs Processed (Cell B8): Count total inventory records using
COUNTA(A2:A5).
- Standard 7-Char SKUs (Cell C8): Count how many cleaned SKUs have exactly 7 characters using
COUNTIF(D2:D5, 7).
Your Sheet Layout
Understanding how the product SKU sanitation worksheet is organized:
- Raw Inventory Data (Columns A & B): Shows Product Name and messy raw SKU strings with extra spaces and mixed casing.
- Cleaned & Validated SKUs (Columns C & D): Produces uppercase trimmed SKUs and counts clean character lengths.
- Master Catalog Summary (Rows 7 & 8): Summarizes total SKU volume and counts standard 7-character compliant codes.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Product Name, Raw SKU |
Raw Records |
Messy unformatted barcode text |
Read-only incoming product records. |
C2:C5 |
Clean SKU |
Text Cleaning |
=UPPER(TRIM(B2)) |
Removes extra spaces and capitalizes text. |
D2:D5 |
Length |
Validation |
=LEN(C2) |
Measures character length of clean SKU. |
B8 |
Total SKUs |
Summary |
=COUNTA(A2:A5) |
Counts total processed SKU rows (4). |
C8 |
Standard 7-Char Count |
Summary |
=COUNTIF(D2:D5, 7) |
Counts SKUs conforming to 7-character length. |
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), clean extra spaces and convert SKU to uppercase using UPPER and TRIM (e.g. =UPPER(TRIM(B2))).
2
In Column D (D2:D5), prefix the cleaned SKU with "CAT-" using concatenation (e.g. ="CAT-" & C2).
3
In cell B8, calculate the total count of cleaned SKU items using COUNTA(C2:C5).
4
In cell B9, count the number of standardized SKUs that start with "CAT-" using COUNTIF(D2:D5, "CAT-*").
Solution & Formula Breakdown
Let's examine how text sanitation functions standardize product keys:
1. Cleaning and Uppercasing with TRIM and UPPER (C2:C5)
=UPPER(TRIM(B2))
Two essential text functions nested together:
TRIM(B2): Removes all leading and trailing spaces, leaving single spaces between words.UPPER(...): Capitalizes every letter character.- Example (
" sku-101a "): TRIM removes spaces → "sku-101a" → UPPER capitalizes → "SKU-101A".
2. Measuring Standard Length with LEN (D2:D5)
=LEN(C2)
Measures the length of the cleaned string to ensure all codes conform to barcode standards.
3. Catalog Summary Rollups (B8 & C8)
- Total SKUs (Cell B8):
=COUNTA(A2:A5) returns 4.
- Standard 7-Char Count (Cell C8):
=COUNTIF(D2:D5, 7) counts compliant 7-character codes.
Always run TRIM before calculating LEN. An accidental trailing space will add invisible extra characters and cause VLOOKUP or MATCH to fail!