Feeling Spacey? Removing Extra Spaces In Excel Text Data


"Extra spaces, what's the big deal?"  May not be, unless you ever want to do searching, matching, sorting, or anything else to the data.  Of course it's a big deal!  Good data is clean data.  Here's the 1-minute answer on how you remove trailing spaces.

Eyeball Method

Do your friends call you "Eagle Eye"?  Well, they'll soon be calling you "Bloodshot Eye" if you spend your days squinting for extra spaces.  As the video shows, most space inclusions are completely undetectable unless you place the cursor at the end of the text.  For large datasets, that would be, well, impractical to say the least.

The SUBSTITUTE() Function

Thankfully, Mr. Gates and friends have provided a handy way to swap out bits of text for other bits of text.  Or remove text completely!

Let's say we wanted to turn "Brady " (with a trailing space) into "Brady" (with no trailing space).  Out in a blank cell somewhere, we would type:

=SUBSTITUTE(D12," ","")

This points to cell D12, looks for " ", and substitutes a null string if it finds it.

What if it doesn't find a space?  Glory be, rather than return an error it simply returns the string unchanged.

Need help with any of these concepts? We're just a phone call away!  Rather than spend your time trying to become an Excel expert, we can fix it for you in minutes!  Give Spreadsheet Sherpa a call at 877.851.0132 today.