Data Cleaning & Text Manipulation

TRIM, UPPER, LOWER, PROPER, TEXTSPLIT, CONCAT, and Flash Fill

« INDEX & MATCH: Dynamic Two-Way Matrix Lookups« Previous Pivot Tables & Pivot Charts: Instant Summarization »Next »

Raw enterprise data frequently arrives with irregular spacing, inconsistent casing, and combined strings. Excel provides dedicated functions and Flash Fill to clean data.

Essential Cleaning Functions

=TRIM(text): Strips leading, trailing, and excessive internal spaces from text.

=PROPER(text): Converts text to Title Case (capitalizes first letter of each word).

=UPPER(text) / =LOWER(text): Converts casing.

=TEXTSPLIT(text, delimiter): Splits a delimited string (like "Jane,Doe") into separate adjacent cells.

Flash Fill (Ctrl + E): Automatically detects patterns as you type and populates the remaining column instantly.

Cleaning Dirty Customer Name Records

Try It Yourself »

// Cell A2 contains dirty text: "   jAnE dOE   "

// Formula to clean spaces and capitalize properly:
=PROPER(TRIM(A2))
// Result: "Jane Doe"

Nesting PROPER and TRIM standardizes messy user inputs.

Google AdSense Slot (inContentBanner)

Set your Publisher ID in src/config/ads.ts to display live ads.

Test Yourself With Exercise

+15 XP

Which Excel keyboard shortcut invokes Flash Fill to automatically complete data patterns based on your examples?

« Previous: INDEX & MATCH: Dynamic Two-Way Matrix Lookups Next: Pivot Tables & Pivot Charts: Instant Summarization »