Why text functions clean messy data in Google Sheets - Dashboard Impact

Dashboard Mode - Why text functions clean messy data

Dashboard Goal

Understand how text functions in Google Sheets help clean messy data for better analysis.

Sample Data

Raw Data	Notes
John Doe	Extra spaces around name
[e-mail]	Mixed case email
[phone]	Phone with symbols
New York	Extra spaces around city
[e-mail]	Uppercase domain in email
Bob O'Neil	Name with apostrophe
123 Main St.	Address with spaces

Dashboard Components

• KPI Card: Clean Names
  Formula: =ARRAYFORMULA(TRIM(A2:A8))
  Removes extra spaces from names and text.
• KPI Card: Lowercase Emails
  Formula: =ARRAYFORMULA(LOWER(A2:A8))
  Converts all email text to lowercase for consistency.
• KPI Card: Extract Phone Numbers
  Formula: =ARRAYFORMULA(REGEXREPLACE(A2:A8, "[^0-9]", ""))
  Removes all non-numeric characters from phone numbers.
• Table: Cleaned Data
  Formula:
  =ARRAYFORMULA({TRIM(A2:A8), LOWER(A2:A8), REGEXREPLACE(A2:A8, "[^0-9]", "")})
  Shows original data cleaned by trimming spaces, lowercasing, and extracting numbers.

Dashboard Layout

+----------------+----------------+----------------+
| Clean Names    | Lowercase Emails| Extract Numbers|
| (KPI Card)    | (KPI Card)     | (KPI Card)     |
+----------------+----------------+----------------+
|                Cleaned Data Table               |
+-------------------------------------------------+

Interactivity

Filters can be added to select specific rows or data types. When a filter is applied, all KPI cards and the cleaned data table update to show only the filtered data.

Self Check

If you add a filter to show only rows containing phone numbers, which components update?

• All KPI cards update to show cleaned names, emails, and extracted numbers only for filtered rows.
• The cleaned data table updates to show only the filtered rows with cleaned data.

Key Result

Shows how text functions like TRIM, LOWER, and REGEXREPLACE clean messy data in Google Sheets.