Excel Text Functions: Extract, Split, Clean, and Transform Text
Text functions let you pull apart and rebuild cell contents without touching the original data. Once you know these, you can clean any messy import in minutes.
Quick Reference
LEFT(text, n)RIGHT(text, n)MID(text, start, n)FIND(char, text)TEXTBEFORE / TEXTAFTERTRIM(text)SUBSTITUTE(text, old, new)TEXTJOIN(", ", TRUE, range)Extract characters
LEFT=LEFT(text, num_chars)Returns the first N characters from the left side of a text string.
Formula
=LEFT("Excel-2026", 5)Returns
"Excel"RIGHT=RIGHT(text, num_chars)Returns the last N characters from the right side of a text string.
Formula
=RIGHT("Excel-2026", 4)Returns
"2026"MID=MID(text, start_num, num_chars)Returns N characters starting at a specific position.
Formula
=MID("Excel-2026", 7, 4)Returns
"2026"LEN=LEN(text)Returns the total number of characters in a string (including spaces).
Formula
=LEN("Excel")Returns
5Find position
FIND=FIND(find_text, within_text, [start_num])Returns the position of a substring (case-sensitive). Returns #VALUE! if not found.
Formula
=FIND("-", "AB-123")Returns
3SEARCH=SEARCH(find_text, within_text, [start_num])Like FIND but case-insensitive and supports wildcards (?, *).
Formula
=SEARCH("ab", "AB-123")Returns
1Split and extract (Excel 365/2021)
TEXTBEFORE=TEXTBEFORE(text, delimiter, [instance_num])Returns everything before the Nth occurrence of a delimiter.
Formula
=TEXTBEFORE("John Smith, Manager", ",")Returns
"John Smith"TEXTAFTER=TEXTAFTER(text, delimiter, [instance_num])Returns everything after the Nth occurrence of a delimiter.
Formula
=TEXTAFTER("John Smith, Manager", ", ")Returns
"Manager"TEXTSPLIT=TEXTSPLIT(text, col_delim, [row_delim])Splits text into a dynamic array by column and/or row delimiter. Spills into multiple cells.
Formula
=TEXTSPLIT("North,South,East", ",")Returns
Three cells: "North" | "South" | "East"Clean and transform
TRIM=TRIM(text)Removes leading, trailing, and extra internal spaces (leaves single spaces between words).
Formula
=TRIM(" Excel ")Returns
"Excel"CLEAN=CLEAN(text)Removes non-printable characters (line breaks, tabs, control characters imported from other systems).
Formula
=CLEAN(A2)Returns
Text with control chars removedPROPER=PROPER(text)Capitalises the first letter of every word. Good for fixing all-caps names.
Formula
=PROPER("JOHN SMITH")Returns
"John Smith"UPPER / LOWER=UPPER(text) / =LOWER(text)Converts text to all uppercase or all lowercase.
Formula
=LOWER("EXCEL")Returns
"excel"SUBSTITUTE=SUBSTITUTE(text, old_text, new_text, [instance_num])Replaces all (or a specific) occurrence of a substring. Case-sensitive.
Formula
=SUBSTITUTE("Jan 2026", "2026", "2027")Returns
"Jan 2027"REPLACE=REPLACE(old_text, start_num, num_chars, new_text)Replaces characters at a specific position — by position, not by value.
Formula
=REPLACE("AB-123", 3, 1, "/")Returns
"AB/123"Join and format
CONCAT / &=CONCAT(text1, text2, ...) or ="A"&"B"Joins multiple text strings. The & operator is simpler for small combinations.
Formula
=A2&" "&B2Returns
"John Smith" (first + space + last)TEXTJOIN=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)Joins a range of cells with a delimiter, with an option to skip blank cells.
Formula
=TEXTJOIN(", ", TRUE, A2:A10)Returns
"North, South, East" (skips blanks)TEXT=TEXT(value, format_text)Converts a number or date to text with a specific format.
Formula
=TEXT(TODAY(), "dd/mm/yyyy")Returns
"14/08/2026" (or current date)Common Text Patterns: Classic vs Modern Formula
For Excel 365/2021 users, TEXTBEFORE and TEXTAFTER replace complex nested formulas. The classic formulas still work in all versions.
| Task | Classic (all versions) | Modern (365/2021) |
|---|---|---|
| Extract first name from "John Smith" | =LEFT(A2, FIND(" ", A2)-1) | =TEXTBEFORE(A2, " ") |
| Extract last name from "John Smith" | =RIGHT(A2, LEN(A2)-FIND(" ", A2)) | =TEXTAFTER(A2, " ") |
| Extract domain from email "user@company.com" | =RIGHT(A2, LEN(A2)-FIND("@", A2)) | =TEXTAFTER(A2, "@") |
| Extract text before the last hyphen in "AB-CD-123" | =LEFT(A2, FIND(CHAR(1), SUBSTITUTE(A2,"-",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1) | =TEXTBEFORE(A2, "-", -1) |
| Remove all spaces from a string | =SUBSTITUTE(A2, " ", "") | — |
| Count how many times a character appears | =LEN(A2)-LEN(SUBSTITUTE(A2, ",", "")) | — |
TRIM vs CLEAN: Which One to Use
These are the two most commonly confused cleaning functions:
- TRIM removes extra spaces — leading, trailing, and multiple spaces between words. Use it on data from other spreadsheets or user-entered data.
- CLEAN removes non-printable characters — line breaks, carriage returns, tabs, and other control characters. Use it on data exported from databases, ERP systems, or web scrapes.
They can be combined: =TRIM(CLEAN(A2)) — this handles both space and control character issues at once.
SUBSTITUTE vs REPLACE: When to Use Each
SUBSTITUTE finds and replaces by value — you tell it what text to find, and it replaces all occurrences. It is case-sensitive. Use it when you know the text you want to swap out.
REPLACE works by position — you tell it the start character and how many characters to overwrite, regardless of what those characters are. Use it when you always need to change characters at a fixed position (e.g., format codes where the 3rd character is always a type indicator).
Frequently Asked Questions
How do I extract text from a cell in Excel?
Use LEFT, RIGHT, or MID for fixed-length extractions. Combine with FIND to extract text relative to a delimiter. In Excel 365/2021, TEXTBEFORE and TEXTAFTER are simpler.
What is the difference between FIND and SEARCH in Excel?
FIND is case-sensitive and does not support wildcards. SEARCH is case-insensitive and supports ? and * wildcards. Use SEARCH for most text-location tasks.
How do I split text in Excel without using Text to Columns?
TEXTBEFORE and TEXTAFTER (Excel 365/2021) extract text before or after a delimiter. TEXTSPLIT splits into a dynamic array. For older versions, combine LEFT/RIGHT with FIND.
Related Guides
Remove Spaces in Excel
TRIM, CLEAN, SUBSTITUTE — when each one works and when they fall short.
Read →
Dynamic Arrays Guide
TEXTSPLIT spills across columns — combine it with dynamic array functions.
Read →
Excel Date Functions
The companion guide — date manipulation with TODAY, DATEDIF, and NETWORKDAYS.
Read →
Excel Formulas Hub
All formula categories — text functions sit alongside lookup, math, and date.
Read →