Dashboard Mode - Why text manipulation cleans data
Goal
Understand how text manipulation formulas help clean messy data for better analysis.
0
0
Why text manipulation cleans data in Excel - Dashboard Impact
Sample Data
| Raw Name | Raw Phone | Raw Email |
|---|---|---|
| Alice Smith | (123) 456-7890 | alice.smith @example.com |
| Bob Johnson | 123.456.7891 | bob_johnson@example.com |
| CHARLIE BROWN | 1234567892 | charlie.brown@example .com |
| diana prince | +1 123 456 7893 | diana.prince@example.com |
| Edward Nigma | 123-456-7894 | edward.nigma @ example.com |
Dashboard Components
- Cleaned Name (Column D): =PROPER(TRIM(A2))
Removes extra spaces and capitalizes names properly.
Example: " Alice Smith " -> "Alice Smith" - Cleaned Phone (Column E): =TEXTJOIN("", TRUE, IFERROR(MID(B2, ROW(INDIRECT("1:"&LEN(B2))), 1)*1, ""))
Extracts only digits from phone numbers.
Example: "(123) 456-7890" -> "1234567890" - Cleaned Email (Column F): =LOWER(TRIM(SUBSTITUTE(C2, " ", "")))
Removes spaces and converts email to lowercase.
Example: "alice.smith @example.com " -> "alice.smith@example.com" - Valid Email? (Column G): =IF(AND(ISNUMBER(FIND("@",F2)), ISNUMBER(FIND(".",F2))), "Yes", "No")
Checks if email contains "@" and "." to flag validity.
Dashboard Layout
+----------------------+----------------------+----------------------+----------------+------------+ | Raw Name | Raw Phone | Raw Email | Cleaned Name | Cleaned | | | | | | Phone | +----------------------+----------------------+----------------------+----------------+------------+ | Alice Smith | (123) 456-7890 | alice.smith @example.com | Alice Smith | 1234567890 | | Bob Johnson | 123.456.7891 | bob_johnson@example.com | Bob Johnson | 1234567891 | | CHARLIE BROWN | 1234567892 | charlie.brown@example .com | Charlie Brown | 1234567892 | | diana prince | +1 123 456 7893 | diana.prince@example.com | Diana Prince | 11234567893| | Edward Nigma | 123-456-7894 | edward.nigma @ example.com | Edward Nigma | 1234567894 | +----------------------+----------------------+----------------------+----------------+------------+ | | | | Cleaned Email | Valid Email| | | | | | | +----------------------+----------------------+----------------------+--------------+------------+ | | | | alice.smith@example.com | Yes | | | | | bob_johnson@example.com | Yes | | | | | charlie.brown@example.com | Yes | | | | | diana.prince@example.com | Yes | | | | | edward.nigma@example.com | Yes | +----------------------+----------------------+----------------------+--------------+------------+
Interactivity
Add a filter to select rows by Valid Email status (Yes/No). When filtered, only rows with matching validity show in all columns, helping focus on clean data.
Self Check
If you filter to show only "No" in Valid Email, which rows remain? (Answer: None in this sample because all emails are valid after cleaning.)
Key Result
Dashboard shows how text formulas clean names, phones, and emails for accurate data.