How to Use TRIM, CLEAN, and SUBSTITUTE in Excel

Introduction

Data imported from other systems often arrives with extra spaces, hidden characters, or inconsistent text. TRIM, CLEAN, and SUBSTITUTE are Excel's text cleaning functions. TRIM removes unwanted spaces, CLEAN strips out non-printable characters, and SUBSTITUTE replaces specific text within a cell.

TRIM

TRIM removes all leading spaces, trailing spaces, and extra spaces between words, leaving only single spaces. The syntax is:

=TRIM(text)

text: The cell or string to clean up.

TRIM in Action

You imported an employee list, but the names came in with spacing problems. Some have spaces before the name, some after, and some have double or triple spaces between first and last names.

The extra spaces are invisible in the cells, but the LEN function reveals them:

Cell	Name	Spacing Issue	LEN
A4	Sarah Mitchell	2 leading, 2 trailing spaces	18
A5	James Williams	Double space between words	15
A6	Maria Garcia	1 leading, 1 trailing space	14
A7	David Lee	Triple space between words	11
A8	Priya Patel	No extra spaces	11

"Sarah Mitchell" is only 14 characters, but A4 has a LEN of 18 because of two leading and two trailing spaces. A5 has a double space between first and last name. A7 has a triple space.

Apply TRIM in column B:

=TRIM(A4)

Cell	Formula	Result	LEN
B4	`=TRIM(A4)`	Sarah Mitchell	14
B5	`=TRIM(A5)`	James Williams	14
B6	`=TRIM(A6)`	Maria Garcia	12
B7	`=TRIM(A7)`	David Lee	9
B8	`=TRIM(A8)`	Priya Patel	11

TRIM removed the leading spaces, trailing spaces, and collapsed every double or triple space down to a single space. Row 8 was already clean, so TRIM returned it unchanged.

Key takeaway: If a VLOOKUP, COUNTIF, or other matching formula returns unexpected results, invisible extra spaces are usually the cause. Wrapping the lookup value in TRIM often fixes it.

CLEAN

CLEAN removes non-printable characters from text. These are characters with ASCII codes 0 through 31, including line breaks, tabs, and control codes that sometimes appear when data is imported from databases or copied from web pages. The syntax is:

=CLEAN(text)

text: The cell or string to clean.

Non-printable characters are invisible in the cell, but they cause problems. A name that looks like "Sarah Johnson" might actually contain a hidden line break, so it won't match "Sarah Johnson" in a lookup or comparison.

In practice, you often pair CLEAN with TRIM since imported data can have both non-printable characters and extra spaces:

=TRIM(CLEAN(A4))

CLEAN strips the hidden characters first, then TRIM handles any spacing issues that remain.

SUBSTITUTE

SUBSTITUTE replaces every occurrence of a specific piece of text with new text. The syntax is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

text: The cell or string to modify.
old_text: The text to find.
new_text: The text to replace it with.
instance_num: Optional. Which occurrence to replace. If omitted, all occurrences are replaced.

Replacing Abbreviations

Your department column uses abbreviations that need to be spelled out:

	A	B
3	Employee	Department
4	Sarah	Mktg
5	James	Eng
6	Maria	Mktg
7	David	Eng
8	Priya	Ops

To replace "Mktg" with "Marketing" in column C:

=SUBSTITUTE(B4, "Mktg", "Marketing")

Employee	Department	Result
Sarah	Mktg	Marketing
James	Eng	Eng
Maria	Mktg	Marketing
David	Eng	Eng
Priya	Ops	Ops

SUBSTITUTE only replaced "Mktg" because that's what the formula searched for. "Eng" and "Ops" passed through unchanged.

To fix all three abbreviations, nest multiple SUBSTITUTE calls:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4, "Mktg", "Marketing"), "Eng", "Engineering"), "Ops", "Operations")

Employee	Department	Result
Sarah	Mktg	Marketing
James	Eng	Engineering
Maria	Mktg	Marketing
David	Eng	Engineering
Priya	Ops	Operations

Each SUBSTITUTE handles one replacement. The innermost one runs first, and each outer SUBSTITUTE works on the result of the one inside it.

Replacing a Specific Occurrence

The optional fourth argument targets a specific occurrence instead of all of them. If cell A4 contains "NY-NY-1042":

=SUBSTITUTE(A4, "NY", "CA", 2)

This replaces only the second "NY", giving you "NY-CA-1042". Without the fourth argument, both would be replaced: "CA-CA-1042".

SUBSTITUTE vs REPLACE

Excel has both SUBSTITUTE and REPLACE, and they work differently:

	SUBSTITUTE	REPLACE
Finds by	Text match	Position (character number)
Syntax	`=SUBSTITUTE(text, old, new)`	`=REPLACE(text, start, num_chars, new)`
Use when	You know what text to replace	You know where in the string to replace
Example	Replace "Mktg" with "Marketing"	Replace characters 3 through 5 with "XYZ"

SUBSTITUTE searches for specific content. REPLACE overwrites characters at a specific position regardless of what they contain. Most text cleaning tasks use SUBSTITUTE because you're looking for specific text, not a character position.

Combining All Three

When cleaning imported data, you often need multiple functions together. A single formula can remove non-printable characters, strip extra spaces, and fix text all at once:

=SUBSTITUTE(TRIM(CLEAN(A4)), "Mktg", "Marketing")

Read this from the inside out:

CLEAN(A4): Remove any non-printable characters.
TRIM(...): Remove leading, trailing, and double spaces.
SUBSTITUTE(...): Replace the abbreviation with the full word.

This pattern covers the majority of text cleaning scenarios. Clean first, trim second, then substitute.

Conclusion

TRIM removes extra spaces, CLEAN strips hidden characters, and SUBSTITUTE replaces specific text. Used together, they handle most text cleaning tasks without manual editing.

For extracting parts of text, check out our guide on LEFT, RIGHT, and MID. For fixing capitalization, see our guide on PROPER, UPPER, and LOWER. To combine cleaned text back together, see our guide on TEXTJOIN.