Guides / Excel

Excel Guide

Excel Text Functions for Cleaning Messy Data

Real-world spreadsheet data is rarely clean — extra spaces, inconsistent capitalization, names crammed into one column. These functions are how you fix it without retyping everything by hand.

TRIM and PROPER: the cleanup duo

TRIM removes extra leading, trailing, and repeated spaces. PROPER capitalizes the first letter of each word. They're almost always used together on messy imported data:

=PROPER(TRIM(A2)) // " JANE doe " → "Jane Doe"

LEFT, RIGHT, MID: extracting substrings

These three pull out a piece of text by position. LEFT and RIGHT count from either end; MID starts partway through:

=LEFT(A2, 3) // first 3 characters =RIGHT(A2, 4) // last 4 characters =MID(A2, 5, 3) // 3 characters, starting at position 5

A common use: pulling an area code from a formatted phone number, or the year out of a date stored as text.

FIND and SUBSTITUTE

FIND locates the position of one piece of text inside another — often used together with LEFT/MID to split text at a delimiter like a comma or space. SUBSTITUTE replaces one piece of text with another, anywhere it appears:

=FIND(",", A2) // position of the first comma =SUBSTITUTE(A2, "-", "") // remove all dashes

CONCATENATE and TEXTJOIN: combining text

To join values from multiple cells into one, use CONCATENATE (or the & operator) — or TEXTJOIN when you want a delimiter and the option to skip blanks automatically:

=CONCATENATE(A2, " ", B2) // first + last name =TEXTJOIN(", ", TRUE, A2:A5) // join a list with commas, skipping blanks
Common mistake: forgetting that TRIM only removes extra spaces between words, not all of them — "a b" becomes "a b", not "ab". It also won't touch certain non-breaking spaces pasted in from web pages; CLEAN or SUBSTITUTE handles those cases.

Try it yourself

Practice TRIM, PROPER, LEFT/RIGHT/MID and more with real interactive exercises and instant feedback.

Start practicing text functions →

Related guides