Whitespace Hygiene, Case Normalization & Delimiter Text Slicing

Who this is for

Stage 4: Data Cleaning, Text Parsing & Hygiene Audits — Designed for learners mastering trim, clean & non-breaking spaces, upper, lower, proper & len width audits, left, right, mid, find & search slicing, nth substitute, replace, exact & rept padding through interactive spreadsheet examples.

What you will practise

• Normalize messy customer names in A2:A5 into B2:B5 using TRIM, then compute total stripped space characters across all rows in C6.
• Sanitize web-pasted product labels in A2:A4 into B2:B4 using TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))), and verify exact catalog matches in C2:C4.
• Normalize Full Name in D2:D4 with PROPER(TRIM(A2)), Email in E2:E4 with LOWER(TRIM(B2)), and Country Code in F2:F4 with UPPER(TRIM(C2)).
• Flag each IBAN in A2:A5 as "Valid" when its space-stripped length equals $E$2 (22) and "Length Error" otherwise in B2:B5, then count errors in B6.
• Extract the 3-character Division Prefix into B2:B5 with LEFT(A2, 3), the 2-character Country Suffix into C2:C5 with RIGHT(A2, 2), and count "DE" shipments in C6.
• From lot codes like "LT-2024-PL09" in A2:A5, extract numeric Batch Year (chars 4..7) into B2:B5 with VALUE(MID(A2, 4, 4)), Plant Code (chars 9..10) into C2:C5 with MID(A2, 9, 2), and count pre-2025 lots in B6.
• Extract the variable-length tag key before ":" from A2:A5 into B2:B5 using IF(ISNUMBER(FIND(":", A2)), LEFT(A2, FIND(":", A2) - 1), "Missing Colon").
• Classify ticket subjects in A2:A5 as "Priority" if they contain keyword $E$2 ("urgent", any case) or "Standard" otherwise in B2:B5, then count Priority tickets in B6.

Your learning path

1. Count Cells With Exact N Characters Using COUNTIF & REPT("?", N)

   Count text codes, SKUs, ZIP codes, or IBANs that match an exact N-character length dynamically using COUNTIF(B2:B7, REPT("?", D2))—and audit mismatched lengths automatically.

2. Extract Clean Hostname / Domain from Any URL (LEFT, FIND & SUBSTITUTE)

   Strip http://, https://, leading www., port numbers (:8443), and trailing /path?query segments from messy referral URLs using sentinel-guarded FIND and SUBSTITUTE.

3. Count Cells Over N Characters (REPT("?", N+1) & "*" Wildcard COUNTIF)

   Count how many text cells exceed a strict character limit N (such as 60-character SEO titles or 160-character SMS messages) using wildcard COUNTIF(range, REPT("?", N + 1) & "*") without helper columns.

4. IF with Wildcards: Pattern Matching via COUNTIF & ISNUMBER(SEARCH)

   Perform wildcard pattern tests (* and ?) inside IF statements—where direct =IF(A2="*PRO*", ...) fails—by pairing IF with COUNTIF(A2, pattern) > 0 or ISNUMBER(SEARCH()).

5. UPPER, LOWER, and PROPER: standardize inconsistent text case

   Convert SKUs to uppercase, email addresses to lowercase, and contact names to title case—while watching how PROPER capitalizes letters after hyphens and punctuation.

6. Extract the Single Word Containing a Specific Substring (@, #, SKU-)

   Pull out the exact space-delimited token containing "@", "#INC-", or "ORD-" from unstructured log messages using the padded-space SUBSTITUTE + SEARCH + MID + TRIM island technique.

7. Highlight Cells That End With a Specific Suffix (RIGHT & LEN Exact Suffix Rule)

   Flag filenames, SKUs, or email domains ending with a target suffix like ".csv" or "-TEMP" using TRIM, RIGHT, and LEN—avoiding wildcard false positives on "?" and "*".

8. Count Total Words Across a Multi-Cell Range Safely (Blank & Space Guards)

   Count total space-delimited words across an entire column or block of notes—without inflating counts on blank cells, double spaces, or space-only entries—using TRIM, TEXTJOIN, LEN, and SUBSTITUTE.