Spaces, line breaks, dashes and other characters make data more readable to us frail humans, but when it comes to parsing data from various sources in Excel, getting the formatting nice and uniform is worth gold. I was spending a lovely afternoon at the office parsing hundreds of lines of hash values but was getting tripped up by the inconsistent use of spaces and line breaks. So, I made myself a little formula to strip all that out. It was easier than I thought it would be.
Check it out.
Use the Microsoft Excel TRIM Function to Remove Extra Spaces
Some people get a little overexcited with the space bar when entering data. Things get worse when you copy and paste them in. To get rid of those pesky extra spaces, use the TRIM function.
Use the Microsoft Excel SUBSTITUTE Function to Remove Special Characters
Trimming is all well and good. But what if some goofball put line breaks into your Excel spreadsheet? Or what if you want to get rid of ALL the spaces? You can do that using SUBSTITUTE.
The syntax for SUBSTITUTE is:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
But Jack, how am I supposed to type a space into a formula?
I’m glad you asked. Just type ” “.
=SUBSTITUTE(B2, " ", "")
In this function, you’re substituting a space with nothing. Nice.
To type something really weird, like a line break, you have to use CHAR(). This lets you pick a specific character that can’t be typed into a formula. The character number for a line break is 10. So, you’d do this:
=SUBSTITUTE(B2, CHAR(10), "")
The optional [instance_num] argument lets you remove say, just the first or second instance of the old text. For example:
=SUBSTITUTE(B2, CHAR(10), "", 1)
You can also nest SUBSTITUTE functions. For example, if you wanted to parse the special characters out of a bunch of phone numbers:
CHAR() and CODE() ANSI/ASCII Code Reference
The trick to SUBSTITUTE() is memorizing every single number to plug into CHAR(). It took me all afternoon, but I’ve finally committed every ANSI character code to memory.
Just kidding. I can’t even remember how old I am let alone what the ANSI code for a space or the @ sign is. Fortunately, you don’t need to. You can either print off this chart from MSDN or use the CODE() Excel function to look up character codes on the fly.
Think of CODE() as the opposite of CHAR().
And there’s your Excel tip for the day. Happy trimming and substituting!