CONCAT / TEXTJOIN
=TEXTJOIN(", ", TRUE, range)
Join text from multiple cells. CONCAT for simple joins, TEXTJOIN when you need a separator and can skip blanks. Both replaced the old CONCATENATE.
TEXTJOIN Complete Guide →Text Functions
Manipulate, split, join, clean, and extract text. Includes the 2024+ REGEX trio for pattern matching — the biggest text update to Excel in a decade.
Text functions turn Excel from a number-crunching tool into a data-cleaning powerhouse. Split full names into first/last. Extract order IDs from log entries. Standardize inconsistent casing. Match complex patterns with regular expressions. Format numbers as text for reports. The 55 functions below cover every text task you'll hit.
The essential five
TEXTSPLIT / TEXTBEFORE / TEXTAFTER
=TEXTSPLIT(A1, ",")
Split text on a delimiter into multiple cells. Replaces the old LEFT/RIGHT/FIND workarounds. TEXTBEFORE and TEXTAFTER extract portions.
TEXTSPLIT Complete Guide →REGEXTEST / REGEXEXTRACT / REGEXREPLACE
=REGEXEXTRACT(A1, "[0-9]+")
Regular expressions in Excel formulas. Match patterns, extract structured data, replace by pattern. Long overdue and hugely powerful.
REGEX Family Guide →TRIM / CLEAN
=TRIM(CLEAN(A1))
The cleanup pair. TRIM removes extra spaces (leading, trailing, doubled). CLEAN removes non-printable characters. Always use both when importing data.
TRIM / CLEAN Guide →Browse by sub-group
Extraction
10 functions
Get portions of text by position or pattern — left N chars, right N chars, middle, or by delimiter.
Splitting & Joining
6 functions
Split one cell into many, or combine many into one. Modern replacements for old string tricks.
Searching & Finding
6 functions
Locate a substring within text. FIND is case-sensitive, SEARCH is not.
Replacing
6 functions
Replace text by position or by matching. SUBSTITUTE is the most flexible for finding.
Casing & Cleanup
8 functions
Standardize casing, remove whitespace, clean non-printable characters.
Formatting
8 functions
Turn numbers into formatted text — currency, percentages, custom formats.
Frequently asked questions
What replaced CONCATENATE?
CONCAT (simple join) and TEXTJOIN (join with a delimiter, optionally skipping blanks). CONCATENATE still works for backward compatibility but is considered legacy.
How do I split full names into first and last?
Use TEXTSPLIT if you're on Excel 365: =TEXTSPLIT(A1, " "). On older Excel, use =LEFT(A1, FIND(" ", A1)-1) for first name and =MID(A1, FIND(" ", A1)+1, 100) for last. Read our full guide.
Are REGEX functions really new in Excel?
Yes — REGEXTEST, REGEXEXTRACT, and REGEXREPLACE launched in 2024 for Excel 365. Before that, regex required VBA or Power Query. Huge productivity upgrade.
Why does my number look like text and formulas fail?
Numbers imported from CSV, web pages, or other systems often get stored as text. Wrap in VALUE() or NUMBERVALUE() to convert. Or use Data → Text to Columns → Finish to convert in bulk.
How do I remove specific characters from a range?
=SUBSTITUTE(A1, "old", "new") for one character. Wrap multiple SUBSTITUTEs for multiple characters, or use REGEXREPLACE with a character class for patterns.
Data cleaning in one click.
The Add-in's Bulk Formatter trims, cleans, splits, and standardizes selected ranges with one command. Skip the formula chains.
Get the Add-in →