Clean, split, join and search text data with TRIM, LEFT, MID, TEXTJOIN and friends.
7 articles
Use TRIM to remove extra spaces, CLEAN to remove hidden non-printing characters, and PROPER to put a capital at the start of each word. Nest them to fix several problems at once: =PROPER(TRIM(A2)) turns " maria gonzalez" into "Maria Gonzalez". Build the clean version in a new column, check it, then paste it as values.
FIND and SEARCH return the position of some text inside a cell, as a number. =FIND(" ",A2) gives 5 for Dana Whitfield, because the space is the 5th character. FIND is case-sensitive; SEARCH ignores capitals and allows wildcards. Both return #VALUE! when the text isn't there, so wrap them in ISNUMBER to test whether a cell contains a word.
To combine text in Excel, join the pieces with &: =A2&" "&B2 turns Nora and Delgado into Nora Delgado. CONCAT does the same with a list of pieces. TEXTJOIN puts the same separator between every piece and can skip empty cells: =TEXTJOIN(", ",TRUE,C2:E2). Type spaces and commas yourself, inside double quotes.
To split a full name in Excel, use =TEXTBEFORE(A2," ") for the first name and =TEXTAFTER(A2," ",-1) for the last name, which also handles middle names. In Excel 2021 and older, use =LEFT(A2,FIND(" ",A2)-1) for the first name. For a one-off job, Data > Text to Columns or Flash Fill splits names without formulas.
LEFT, RIGHT and MID pull part of the text out of a cell. LEFT(text, num_chars) takes characters from the start, RIGHT(text, num_chars) takes them from the end, and MID(text, start_num, num_chars) takes them from any position. For TX-ELC-40512, LEFT(A2,2) gives TX, MID(A2,4,3) gives ELC and RIGHT(A2,5) gives 40512.
TEXTBEFORE returns the text before a delimiter, and TEXTAFTER returns the text after it. =TEXTBEFORE(A2,"@") gives the username of an email, and =TEXTAFTER(A2,"@") gives the domain. A third argument picks which occurrence: -1 means the last one. They need Microsoft 365, Excel for the web or Excel 2024; in older Excel, use LEFT or MID with FIND.
The TEXT function turns a number or date into text with the format you choose: =TEXT(value, format_text). For example, =TEXT(B2,"mmm d, yyyy") shows a date as Feb 3, 2025, and =TEXT(C2,"$#,##0.00") shows $1,250.50. Use it when you join numbers or dates to words, because & otherwise shows the raw stored number.