How-To

Excel Tip: Remove Spaces and Line Breaks from Cells

Removing special characters and spaces and Microsoft Excel makes it easier to compare and process data.


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.

=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?

But Jack, how am I supposed to type a space into a formula?

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!


1 Comment

1 Comment

  1. A Smead  

    Do you ever need to know something, but don’t realize you need to know it until you see it? Well, I definitely needed to know this, so thank you! I struggle every month with an employee report pulled from an antiquated system that seems to add in patches of 20-40 spaces for no reason at all, except to drive me crazy. NO MORE!!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top