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.
=TRIM(text)
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])
Got that? I’m glad you asked. Just type ” “. Like this:
=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:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,CHAR(40),”"),CHAR(41),""),CHAR(45),""),CHAR(32),""),CHAR(46),"")
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.
=CODE(text)
Think of CODE() as the opposite of CHAR().
Conclusion
And there’s your Excel tip for the day. Happy trimming and substituting! Comment
Δ
