TRIM and CLEAN in Excel - Dashboard Guide

Dashboard Mode - TRIM and CLEAN

Dashboard Goal

Clean up messy text data by removing extra spaces and non-printable characters to prepare for accurate analysis.

Sample Data

Raw Text	Expected Clean Text
" Hello World "	"Hello World"
"GoodMorning"	"GoodMorning"
" ExcelSheet "	"ExcelSheet"
" Data Analysis "	"Data Analysis"
"LineBreak"	"LineBreak"
" Clean Text "	"Clean Text"
" TRIM and CLEAN "	"TRIM and CLEAN"

Dashboard Components

• Original Text Column: Shows the raw text data with extra spaces and hidden characters.
• Cleaned Text Column: Uses formula =TRIM(CLEAN(A2)) to remove extra spaces and non-printable characters from the raw text in cell A2.
• Count of Cleaned Characters: Uses formula =LEN(B2) to count the number of characters in the cleaned text in cell B2, showing the effect of cleaning.
• Summary Card: Total Rows Cleaned: Uses formula =SUBTOTAL(103, A2:A8) to count how many visible rows of data were cleaned.
• Summary Card: Average Length of Cleaned Text: Uses formula =SUBTOTAL(101, C2:C8) to find the average length of visible cleaned text strings.

Dashboard Layout

+----------------------+----------------------+----------------------+
| Original Text        | Cleaned Text         | Characters Count     |
| (Raw Data)           | (TRIM+CLEAN Result)  | (Length of Cleaned)  |
| A2:A8                | B2:B8                | C2:C8                |
+----------------------+----------------------+----------------------+
|                      Summary Cards:                             |
| +----------------+  +-----------------------------+           |
| | Total Rows     |  | Average Length of Cleaned    |           |
| | Cleaned       |  | Text                        |           |
| | =SUBTOTAL(103, |  | =SUBTOTAL(101, C2:C8)       |           |
| | A2:A8)        |  |                             |           |
| +----------------+  +-----------------------------+           |
+---------------------------------------------------------------+

Interactivity

Add a filter or slicer on the Original Text column to select specific rows. When you filter rows, the Cleaned Text and Characters Count columns update automatically to show only the filtered data. The summary cards also update to reflect the count and average length of the filtered rows.

Self Check

If you add a filter to show only rows where the Original Text contains the word "Excel", which components update?

• The Cleaned Text column updates to show only cleaned texts with "Excel".
• The Characters Count column updates to count characters of filtered cleaned texts.
• The Summary Cards update: Total Rows Cleaned shows the count of filtered rows, and Average Length shows the average length of those filtered cleaned texts.

Key Result

Dashboard cleans text data using TRIM and CLEAN, showing cleaned text, character counts, and summary stats.