When working with Excel data from outside sources (like websites, PDFs, or copy-paste from emails), you’ll often get messy text full of extra spaces or wrong words/symbols.
Luckily, Excel gives you two powerful functions to clean it up:
TRIM– Removes extra spacesSUBSTITUTE– Replaces specific words or characters
Let’s break them down with simple examples.
Why Are These Functions Important?
These functions help when:
- You copy-paste data with random spaces
- You need to replace or correct specific words or characters
- You’re preparing data for VLOOKUP or analysis
Function 1: TRIM
What it does:
Removes all extra spaces, keeping just one space between words.
Syntax:
=TRIM(text)
Example:
| A (Original Text) | B (After TRIM) |
|---|---|
" Smart Tejas " | =TRIM(A2) → Smart Tejas |
" Excel is fun " | Excel is fun |
" Hello World! " | Hello World! |
Use TRIM when:
- Data looks correct but formulas/VLOOKUP aren’t working
- There are hidden spaces at beginning or end
Function 2: SUBSTITUTE
What it does:
Replaces specific words, letters, or characters in a text.
Syntax:
=SUBSTITUTE(text, old_text, new_text)
Examples:
| A (Original Text) | Formula | Result |
|---|---|---|
| Hello Tejas | =SUBSTITUTE(A2, "Tejas", "World") | Hello World |
| 123-456-789 | =SUBSTITUTE(A3, "-", "/") | 123/456/789 |
| 2024.05.20 | =SUBSTITUTE(A4, ".", "-") | 2024-05-20 |
| excel is powerful | =SUBSTITUTE(A5, " ", "_") | excel_is_powerful |
| Smart -Tejas | =SUBSTITUTE(A6, " -", "-") | Smart-Tejas |
Explanation:
In the last example:
=SUBSTITUTE(A6, " -", "-")
Note: This removes the unwanted space before the hyphen and returns a cleaner text:
Smart -Tejas → Smart-Tejas
Tip:
- You can use SUBSTITUTE multiple times for different corrections, like:
=SUBSTITUTE(SUBSTITUTE(A2, " -", "-"), "Tejas", "World")
Use SUBSTITUTE when:
- You want to fix spelling or standardize names
- Convert special characters (like changing
-to/)- Replace spaces with underscores, etc.
Summary Table
| Function | Use For | Example |
|---|---|---|
TRIM | Remove extra spaces | =TRIM(" Hello World ") → Hello World |
SUBSTITUTE | Replace specific word/character | =SUBSTITUTE("Tejas", "Tejas", "World") → World |
Bonus Tip: Combine Both!
=TRIM(SUBSTITUTE(A2, "-", " "))
👉 First replaces - with space, then removes extra spaces.
What’s Next?
In the next post, we’ll learn about the Date & Time Functions in Excel.