11 Excel Text Functions for Data Cleanup in 2026 — Fix Messy Data Fast
Home› 11 Excel Text Functions for Data Cleanup
🧹 Top List · Data Cleanup

11 Excel text functions for data cleanup in 2026

Real-world data arrives messy. Extra spaces, mixed cases, embedded non-printable characters from PDF copies, phone numbers with random formatting, dates as text — analysts spend more time cleaning data than analyzing it. This guide covers the 11 text functions that fix 95% of messy data problems — with before/after examples for every function so you can pattern-match your specific cleanup challenge.

🧹 The data cleanup mindset

Data cleanup isn't about running one magic function — it's about chaining 2-4 text functions together to normalize a column. TRIM removes spaces, CLEAN removes non-printable characters, PROPER normalizes case, SUBSTITUTE swaps unwanted characters. Master the individual functions first, then combine them into workflows. Every messy dataset gets clean with the right pipeline.

The 5 most common data quality problems

Before the functions, understand the problems they solve. Nearly every messy dataset has one of these five issues:

1. Extra whitespace. Leading spaces, trailing spaces, or multiple spaces between words. Invisible to the eye but breaks every VLOOKUP and pivot table grouping. Fixed with TRIM.

2. Non-printable characters. When you copy from PDF, web pages, or other apps, invisible ASCII control characters come along. Fixed with CLEAN.

3. Inconsistent capitalization. "john smith", "JOHN SMITH", "John smith", "John Smith" — same person, four different values. Fixed with UPPER, LOWER, or PROPER.

4. Embedded delimiters or formatting. Phone numbers with mixed formats "(555) 123-4567" vs "555.123.4567", currency as text with $ and commas. Fixed with SUBSTITUTE or TEXTSPLIT.

5. Combined fields. Full names in one column that need splitting into first/last, addresses with city/state/zip merged, product codes with embedded metadata. Fixed with LEFT/RIGHT/MID or the modern TEXTBEFORE/TEXTAFTER/TEXTSPLIT.


1
Essential Classic Beginner
TRIM The #1 Cleanup Function

Remove leading, trailing, and duplicate spaces

TRIM is the most-used text function in professional data work. It removes leading spaces, trailing spaces, and reduces runs of internal spaces to single spaces. If your data comes from any external source, wrap it in TRIM before doing anything else. Whitespace is invisible but breaks lookups, joins, and pivot groupings.

Messy Input
" John Smith "
Formula
=TRIM(A2)
Clean Output
"John Smith"
Note: TRIM only removes regular ASCII spaces (character 32), not non-breaking spaces (character 160) common in web copy.
Real use case: Any column that came from a CRM export, web scrape, PDF copy, or system integration. Wrap all text lookups in TRIM on both sides to prevent phantom mismatches: =XLOOKUP(TRIM(A2), TRIM(LookupRange), ReturnRange).
2
Essential Classic Beginner
CLEAN The Invisible Character Killer

Remove non-printable ASCII characters

CLEAN removes the first 32 ASCII characters — control characters like line feeds, tabs, and other non-printable garbage that come from PDF or web copies. Character 160 (non-breaking space) is NOT removed by CLEAN, so pair with SUBSTITUTE for full cleanup.

Messy Input
"Report Q3⏎ (with hidden chars)"
Formula
=CLEAN(A2)
Clean Output
"Report Q3 (with hidden chars)"
The ⏎ represents an embedded line break (character 10) — invisible in the cell but breaks concatenation and matching.
Combine with: Always pair with TRIM: =TRIM(CLEAN(A2)). For non-breaking spaces from web copy, add SUBSTITUTE: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
Real use case: Anytime data was copy-pasted from a PDF, an email, a web page, or an app that emits control characters. The "values look identical but don't match" problem is almost always fixed with CLEAN + TRIM.
3
Essential Classic Beginner
PROPER / UPPER / LOWER Case Normalization Trio

Convert text to Title Case, UPPERCASE, or lowercase

Three related functions for consistent capitalization: PROPER capitalizes the first letter of every word (Title Case), UPPER makes everything uppercase, LOWER makes everything lowercase. Essential for normalizing text that comes from multiple sources with inconsistent capitalization habits.

Messy Input
"jOHn SmITh"
Formula
=PROPER(A2)
Clean Output
"John Smith"
PROPER also capitalizes after non-letters — "MCDONALD" becomes "Mcdonald" which is often wrong. Handle exceptions with SUBSTITUTE.
Real use case: Normalizing names, city names, and titles from data-entry systems where users capitalize inconsistently. UPPER for country codes and abbreviations. LOWER for email addresses (emails should be normalized to lowercase for reliable matching).
4
Essential Classic Beginner
SUBSTITUTE The Find and Replace Workhorse

Replace specific text with other text

SUBSTITUTE finds a specific string within text and replaces it with something else. Unlike Find & Replace (which modifies the source), SUBSTITUTE creates a new value — perfect for creating cleaned columns without destroying originals. Optional 4th argument lets you replace only specific occurrences.

Messy Input
"Phone: (555) 123-4567"
Formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")
Clean Output
"Phone: 555 1234567"
Nest SUBSTITUTE to remove multiple characters. Add TRIM outside to collapse resulting spaces.
Real use case: Standardizing phone numbers, removing currency symbols before conversion to numbers, replacing separators (commas to pipes, hyphens to spaces), stripping HTML tags from scraped content. Most common cleanup function after TRIM.
5
Classic Intermediate
LEFT / RIGHT / MID Position-Based Extraction

Extract characters from left, right, or middle

The three positional-extraction functions. LEFT returns N characters from the start, RIGHT returns N characters from the end, MID returns characters starting at a specific position. Perfect when your data has predictable positions — SKUs, employee IDs, formatted codes. Less useful when structure varies (use TEXTBEFORE/TEXTAFTER instead).

Messy Input
"PROD-2024-XL-BLUE"
Formula
=LEFT(A2, 4) → category =MID(A2, 6, 4) → year =RIGHT(A2, 4) → color
Clean Output
"PROD" / "2024" / "BLUE"
Works only for fixed-position data. For variable-length parts, combine with FIND or use TEXTSPLIT.
Real use case: Extracting components from structured identifiers — SKUs, part numbers, employee IDs, invoice numbers. First 3 chars of a country code, last 4 digits of a credit card, middle segment of a compound ID.
6
Classic Beginner
LEN The Validation Helper

Count characters in text

LEN returns the number of characters in a string. Foundational for validation ("phone numbers should be 10 characters") and for building extraction formulas that need to compute "everything except the last N characters". Also useful for spotting outliers — a "Name" column with LEN > 50 probably has data quality issues.

Messy Input
"5551234567"
Formula
=LEN(A2)=10
Clean Output
TRUE (valid US phone length)
Combine with: Wrap in IF to flag issues: =IF(LEN(A2)<>10, "Bad phone", "OK"). Combine with LEFT for "everything except last N chars": =LEFT(A2, LEN(A2)-4).
Real use case: Data validation — phone numbers should be 10 chars, SSNs should be 9, credit cards should be 15-16. Spotting outliers in text columns (unusually short or long values). Building extraction formulas that reference text length.
7
Classic Intermediate
FIND / SEARCH The Position Locator

Locate the position of one string within another

FIND and SEARCH both return the starting position of one text string inside another. Key difference: FIND is case-sensitive, SEARCH is case-insensitive and supports wildcards (? and *). Both return #VALUE! if the substring isn't found — usually wrapped in IFERROR for graceful handling.

Messy Input
"john.smith@company.com"
Formula
=FIND("@", A2)
Clean Output
11 (position of @ symbol)
Combine with LEFT to extract email username: =LEFT(A2, FIND("@",A2)-1)
Real use case: Extracting parts of strings when the delimiter position varies. Finding where a keyword appears in text for validation. Building extraction formulas that adapt to variable-length data. In Excel 365, TEXTBEFORE and TEXTAFTER are simpler alternatives.
8
Modern Beginner
TEXTJOIN The Modern Concatenator

Join multiple strings with a delimiter

TEXTJOIN combines multiple text values with a specified delimiter and optionally skips empty cells. Replaces the old pattern of A1 & ", " & A2 & ", " & A3 which produces "A, , C" when B is empty. TEXTJOIN handles the empty-cell problem elegantly with a single argument.

Messy Input
A1="John" B1="" C1="Smith" D1="Sales"
Formula
=TEXTJOIN(" | ", TRUE, A1:D1)
Clean Output
"John | Smith | Sales"
The TRUE argument (ignore_empty) skips B1 automatically. Without it: "John | | Smith | Sales".
Real use case: Building full addresses from separate fields (skipping empty apartment lines), creating comma-separated lists from ranges, combining name parts, building dynamic report titles that skip missing values. TEXTJOIN dedicated page.
9
Modern Intermediate
TEXTSPLIT The Modern Splitter

Split text into an array by delimiter

TEXTSPLIT breaks a single text value into multiple pieces based on a delimiter, spilling the results into adjacent cells. The modern replacement for Text-to-Columns (which is destructive and doesn't update). TEXTSPLIT is a formula so it recalculates as source data changes. Excel 365+ only.

Messy Input
"Smith, John, Sales, 55000"
Formula
=TEXTSPLIT(A2, ", ")
Clean Output
"Smith" | "John" | "Sales" | "55000"
Result spills into 4 cells across a single row. Use ; separator for row splits, both for 2D matrix splits.
Real use case: Splitting CSV strings without importing, parsing full names into components, extracting fields from delimited log entries, breaking apart tag lists. Auto-updates as source data changes (unlike Text-to-Columns).
10
Modern Intermediate
TEXTBEFORE / TEXTAFTER Modern Extraction by Delimiter

Extract everything before or after a specific string

TEXTBEFORE returns everything to the left of a specified delimiter. TEXTAFTER returns everything to the right. Together they replace the LEFT+FIND / MID+LEN+FIND patterns from earlier Excel eras with cleaner, more readable formulas. Excel 365+ only. Combine them for extracting middle segments.

Messy Input
"john.smith@company.com"
Formula
=TEXTBEFORE(A2, "@") → username =TEXTAFTER(A2, "@") → domain
Clean Output
"john.smith" / "company.com"
Nest to extract middle: =TEXTBEFORE(TEXTAFTER(email, "@"), ".") returns "company" (domain name only, no extension).
Real use case: The modern way to extract emails, URLs, file extensions, path components, and any delimited string parts. Cleaner than LEFT+FIND syntax and self-documenting — the formula reads like English.
11
Classic Intermediate
VALUE / TEXT The Text ↔ Number Converters

Convert between text and numeric representations

VALUE converts text that looks like a number ("1,234") into an actual number (1234) usable in calculations. TEXT does the reverse — converting numbers into formatted text strings ("$1,234.56"). Essential when data arrives as text from external sources (CSV imports, database exports, PDFs) and needs to become computable.

Messy Input
"$1,234.56" (stored as text)
Formula
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""))
Clean Output
1234.56 (usable in SUM, etc.)
TEXT works the other way: =TEXT(1234.56, "$#,##0.00") returns "$1,234.56" as text — useful for building formatted labels.
Real use case: Converting CSV imports where numbers arrived as text, cleaning currency values with symbols, building formatted labels for dashboards ("$45.2K MRR"), preparing values for cross-system exports where formatting needs to be baked in.

3 real-world cleanup workflows

Individual functions are the building blocks. Real cleanup work is chaining them together. Three patterns you'll use constantly:

Workflow 1 — Full text normalization

1
Strip non-breaking spaces with SUBSTITUTE(A2, CHAR(160), " ")
2
Remove non-printable characters with CLEAN(...)
3
Collapse extra whitespace with TRIM(...)
4
Normalize case with PROPER(...)
=PROPER(TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))))

Workflow 2 — Split full name into first and last

1
Extract first name with TEXTBEFORE(A2, " ")
2
Extract last name with TEXTAFTER(A2, " ") — takes everything after the last matching delimiter
3
Or split both at once with TEXTSPLIT(A2, " ")
=TEXTSPLIT(TRIM(A2), " ")

Workflow 3 — Clean currency text to usable number

1
Remove currency symbol with SUBSTITUTE(A2, "$", "")
2
Remove thousands separators with SUBSTITUTE(..., ",", "")
3
Trim leftover whitespace with TRIM(...)
4
Convert to actual number with VALUE(...)
=VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", "")))

Modern vs classic text functions

Excel 365 introduced TEXTSPLIT, TEXTBEFORE, and TEXTAFTER — modernizing text operations that used to require nested LEFT/RIGHT/MID/FIND combinations. Here's when to use which:

TaskModern (365+)Classic (all versions)
Extract text before "@"TEXTBEFORE(A2, "@")LEFT(A2, FIND("@", A2)-1)
Extract text after "@"TEXTAFTER(A2, "@")MID(A2, FIND("@", A2)+1, LEN(A2))
Split by commaTEXTSPLIT(A2, ",")Text-to-Columns (destructive) or nested formulas
Join with delimiter, skip blanksTEXTJOIN(", ", TRUE, range)Multiple & concatenations with IF checks
The 365 upgrade calculus

If you're on Excel 365 or 2024+, use the modern functions. They're shorter, more readable, and less error-prone. If you're on 2019 or earlier, you're stuck with classic patterns — but they still work, they just take more nesting. When sharing workbooks with people on older versions, stick to classic functions to avoid compatibility issues. Compare CONCATENATE vs TEXTJOIN vs Ampersand for detailed guidance.

Common cleanup mistakes to avoid

Editing originals instead of building cleaned columns. Never overwrite source data. Always add a "Cleaned_Name" column next to "Name". This preserves the original for troubleshooting when your cleanup logic misses edge cases.

Not documenting your cleanup logic. Six months later you'll look at =PROPER(TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))) and wonder what problem it solved. Add a comment or a header row explaining what each function is doing and why.

Assuming one pass fixes everything. Real messy data has multiple issues. Run TRIM, then check for remaining issues, then run CLEAN, check again, then PROPER. Don't assume the first cleanup pass caught everything.

Cleaning at query time instead of source time. If you're constantly cleaning the same column, fix the source. Push cleanup into the export process, the database view, or the Power Query step — anywhere upstream of the analyst workbook.

Forgetting to convert back to values. Formula-based cleanup keeps calculating on every workbook open. For static reference data, copy the cleaned column and Paste Values back over itself to lock in the clean data and speed up the workbook.

Related resources

Frequently asked questions

What is the most useful text function in Excel for data cleanup?

TRIM is the most useful text function for data cleanup because extra whitespace is the #1 cause of failed lookups, mismatched joins, and formula errors. TRIM removes leading, trailing, and duplicate internal spaces in one operation. Combine with CLEAN to remove non-printable characters and PROPER for case normalization to handle 90% of common data quality issues.

What is the difference between FIND and SEARCH in Excel?

FIND is case-sensitive — searching for "ABC" will not match "abc". SEARCH is case-insensitive and accepts wildcards like ? and *. For most cleanup work SEARCH is more forgiving. Use FIND when case matters, such as extracting a specific tag or matching an exact identifier where capitalization is meaningful.

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE replaces text based on matching content — "find X and replace with Y". REPLACE replaces text based on position — "replace 3 characters starting at position 5". SUBSTITUTE is far more common for cleanup because you rarely know exact positions but you often know what needs replacing. REPLACE is useful for formatting fixed-width strings like SSNs or credit cards.

How do I split full names into first and last name in Excel?

In Excel 365, use TEXTSPLIT: =TEXTSPLIT(A2, " ") returns first and last as an array. Or use TEXTBEFORE for first name and TEXTAFTER for last name. In older Excel versions, combine LEFT with FIND for first name and MID for last name, or use Text to Columns from the Data tab as a one-time conversion.

How do I remove non-printable characters copied from PDFs or the web?

Use CLEAN to remove non-printable ASCII characters (character codes 0-31). Wrap with TRIM to also remove extra spaces that often accompany PDF/web copy-paste: =TRIM(CLEAN(A2)). For persistent non-breaking spaces (character 160) that CLEAN misses, combine with SUBSTITUTE: =TRIM(SUBSTITUTE(CLEAN(A2), CHAR(160), " ")).

Why do my VLOOKUP or XLOOKUP formulas return #N/A even when the values match?

This is almost always caused by hidden whitespace or non-printable characters in one or both sides of the lookup. The values look identical to the human eye but Excel sees "John Smith" and "John Smith " as different strings. Wrap both sides in TRIM and CLEAN to fix. This single fix resolves most "the values look the same but don't match" problems in lookups.

Can I combine multiple text functions in one formula?

Yes, and this is the standard workflow for real cleanup. Common combinations include =TRIM(CLEAN(PROPER(A2))) for full normalization of a text value, =TEXTAFTER(TEXTBEFORE(email,".com"),"@") to extract a domain without extension, and =VALUE(SUBSTITUTE(A2,",","")) to convert comma-formatted number strings to numbers. Nested text functions are efficient because they work in a single cell without helper columns.

Should I use formulas or Power Query for data cleanup?

Use text functions for occasional cleanup or when the cleanup is one part of a larger analysis workbook. Use Power Query for repeated cleanup workflows where the same messy data arrives daily or weekly — Power Query saves and re-runs the entire cleanup pipeline. Rule of thumb: if you'd have to redo the cleanup manually more than 3 times, move it to Power Query.

How do I check if a text column has hidden issues?

Add a helper column with =LEN(A2) and scan for unusual values — very short or very long strings often indicate quality issues. Compare =LEN(A2) with =LEN(TRIM(A2)) — if they differ, the column has extra spaces. Use =CODE(LEFT(A2,1)) to check the character code of the first character — anything under 32 or code 160 signals non-printable characters that TRIM won't fix.

· · ·

Bottom line: Master TRIM, CLEAN, and PROPER first — they fix 80% of cleanup issues. Add SUBSTITUTE and LEN for validation. Learn the modern TEXTSPLIT / TEXTBEFORE / TEXTAFTER trio if you're on Excel 365. Chain them into workflows and any messy dataset gets clean.