Remove Spaces in Excel

Check out the video ​below for an explanation on how to remove spaces in Excel.

play

Video summary

Here's a quick recap of the above video:

To remove spaces, you can use the function =​TRIM(​Text​)​. This function removes all spaces from a text​​​, except for single spaces between words. So a text like:

       ​Many     spaces     text      

(Note the spaces at the start and end of the ​text)

​Will become:

Many spaces text

​To remove every single space, you can use =​SUBSTITUTE(​Text​" ", ​""​)​. This will simply remove every space from a given text.​​​

​You can remove line breaks with the function =CLEAN(​​Text​)​.

To replace line breaks with a different character, like a space​, you can use the Substitute function again. ​​​​​​=​SUBSTITUTE(​Text​​CHAR(10), ​" "​)​ will substitute line breaks for spaces in the given text.​​​ CHAR(10) in the above function is the ASCII (internal ​character representation) value of the ​new line​.

​More on removing spaces

​Syntax

​Here are the syntaxes of the functions shown in the video:

​​=​TRIM(​Text​)
Removes every space from a text except for single spaces between words.

​=CLEAN(​​Text​)
​Removes line breaks and​​​​ non-printable characters.

=​SUBSTITUTE(​Text​Old text, ​New text, [​Instance num])​
Substitutes any occurrences of ​Old text in ​​Text with ​New text.

​Alternative: using Find ​and Replace to remove spaces

​We can make Excel search for any double spaces "  " and replace them by single spaces " ". This will reduce any group of spaces to a single space​. A drawback of this method is that ​it will only reduce leading and trailing spaces ​to a single space instead of completely removing them.

  • Open the Find and Replace window by clicking on Find & Select on the Home tab of the Ribbon and then Replace... in the dropdown that shows. Alternatively, you can press ​​Ctrl + ​​​​H (Windows) or ​⌘Command + ​​​​H (Mac) to open the window.
  • ​In the Find and Replace window ​put 2 spaces in the Find what: field and 1 space in the Replace with: field.
  • Click on Replace All and press OK on the popup. ​
  • Keep clicking Replace All and pressing OK until no more double spaces can be found.

How do you create line breaks?

We've talked about removing line breaks in the video. But before removing line breaks, you have to create them first. 

You can create a line break by pressing Alt + Enter in a cell.

​We talk about another method of removing line breaks ​in ​our Find and Replace blog post.

That's it

​Did we help you with removing spaces from your workbook? Do you have other techniques to remove spaces in Excel? Let us know in the comments.


You may also like

How to download fonts and install them in Excel

How to download fonts and install them in Excel

Is there a ‘minus sum’ for spreadsheets?

Is there a ‘minus sum’ for spreadsheets?
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

>