You get a list of phone numbers with hyphens, brackets and spaces, and another of customers with names carrying extra spaces. Lookups fail to match even though the data looks identical at a glance.
How to use SUBSTITUTE and TRIM Formula step by step
1. Choose the task
Replace a text, change only the Nth occurrence, remove a character, make two changes in a row, clean spaces, line breaks, spaces copied from the web or count occurrences.
2. Give the text
Type the cell to clean and, if you change something, the text you look for and the one that replaces it. Spaces you type inside are kept.
3. Copy the formula
Paste it in a helper column and drag it down. Then copy and paste as values if you want to keep the clean result.
The Step Count: Traditional Friction vs. KorexTools
Cleaning the list cell by cell (4 steps)
- Open the cell for editing.
- Delete the stray spaces and signs.
- Move to the next cell.
- Repeat until the end of the list.
With the KorexTools “SUBSTITUTE and TRIM Formula” builder (3 steps)
- You choose the cleaning task.
- You give the cell and the texts.
- You copy the formula and paste the result as values.
Almost every dirty-data problem in Excel is solved with two functions and a trick
SUBSTITUTE swaps one text for another inside a cell: it takes the cell, what you look for and what replaces it, and optionally which occurrence to change. If the replacement is an empty text, it deletes what it finds, which is the fastest way to strip hyphens, dots or letters from a code. It is case sensitive.
TRIM removes spaces at the start and end and leaves a single one between words, and CLEAN strips line breaks and other non-printing characters. The spaces that arrive when you copy from a web page are usually non-breaking spaces, which TRIM does not recognise; they are fixed by substituting character 160 with a normal space.
Which function solves which case
- •Swap hyphens for slashes: SUBSTITUTE.
- •A code with spaces that fail to match in a lookup: TRIM.
- •Text pasted with line breaks: CLEAN.
- •Spaces that will not go: SUBSTITUTE with character 160.
With “ Ana-Perez ” in A2
| Task | Formula | Result |
|---|---|---|
| Remove extra spaces | =TRIM(A2) | Ana-Perez |
| Swap the hyphen for a space | =SUBSTITUTE(TRIM(A2),"-"," ") | Ana Perez |
| Count the hyphens | =(LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))/LEN("-") | 1 |
Common Mistakes and How to Avoid Them
- ⚠️Overwriting the original cell and losing the data with no copy.
- ⚠️Forgetting that SUBSTITUTE tells capitals from lower case.
Frequently Asked Questions
Why does TRIM not remove a space I can see?
It is probably a non-breaking space, typical of text copied from a web page. Choose the stubborn-spaces option and the formula turns it into a normal space before cleaning.
Is SUBSTITUTE case sensitive?
Yes. If you search for “ana” it will not change “Ana”. To ignore case you can first convert the text to lower case with the upper and lower case tool, then substitute.
How do I change only the second occurrence?
With the replace-the-Nth-occurrence option: type 2 and SUBSTITUTE changes only that one, leaving the first as it was.
What I would do if I were in your shoes
«Clean a list before you search it: most failing lookups have a data problem, not a formula problem.» — DataKorex Engineering Team.