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.
Quick navigation — jump to any function
- TRIM — Extra spaces
- CLEAN — Non-printable chars
- PROPER/UPPER/LOWER — Case
- SUBSTITUTE — Find/replace
- LEFT/RIGHT/MID — Extract by position
- LEN — Character count
- FIND/SEARCH — Locate position
- TEXTJOIN — Concatenate
- TEXTSPLIT — Split into array
- TEXTBEFORE/TEXTAFTER — Extract by delimiter
- VALUE/TEXT — Number ↔ text conversion
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.
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.
=XLOOKUP(TRIM(A2), TRIM(LookupRange), ReturnRange).
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.
=TRIM(CLEAN(A2)). For non-breaking spaces from web copy, add SUBSTITUTE: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
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.
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.
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).
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.
=IF(LEN(A2)<>10, "Bad phone", "OK"). Combine with LEFT for "everything except last N chars": =LEFT(A2, LEN(A2)-4).
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.
=LEFT(A2, FIND("@",A2)-1)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.
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.
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.
=TEXTBEFORE(TEXTAFTER(email, "@"), ".") returns "company" (domain name only, no extension).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.
=TEXT(1234.56, "$#,##0.00") returns "$1,234.56" as text — useful for building formatted labels.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
SUBSTITUTE(A2, CHAR(160), " ")CLEAN(...)TRIM(...)PROPER(...)Workflow 2 — Split full name into first and last
TEXTBEFORE(A2, " ")TEXTAFTER(A2, " ") — takes everything after the last matching delimiterTEXTSPLIT(A2, " ")Workflow 3 — Clean currency text to usable number
SUBSTITUTE(A2, "$", "")SUBSTITUTE(..., ",", "")TRIM(...)VALUE(...)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:
| Task | Modern (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 comma | TEXTSPLIT(A2, ",") | Text-to-Columns (destructive) or nested formulas |
| Join with delimiter, skip blanks | TEXTJOIN(", ", TRUE, range) | Multiple & concatenations with IF checks |
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
The complete text-processing toolkit
Dedicated pages for the most-used text functions, plus the comparison guides that help you pick between similar options.
Text Functions Category → TRIM Function → TEXTJOIN Function →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.