The SUBSTITUTE function is an easy way to replace characters in an entry with something else. Here is an example of a cell with data entered:
I want to replace “abc” with “def”. In the cell next to it I enter the SUBSTITUTE function:
First I click on the cell I want to change followed by the old text, then the new in quotes:
And the substitution is complete:
Now let’s use SUBSTITUTE to remove spaces that are in the middle of an entry. Start out the same way, but for the old text use a space in the quotes, and for the new text, use closed quotes:
And the space is removed:
This also works when you have multiple spaces in the entry:
And there you go! BTW, this tip came from a follower of my Excel Bytes blog. If you have a tip or suggestion, you can e-mail me through my blog or just comment on the blog post and I will see what I can do to accommodate your request! Happy Excelling!