Video: How to remove extra spaces in Excel

Learn how to delete spaces in Excel using standard functions or a special add-in.

When you want to trim text and remove leading or trailing spaces that impede with sorting and filtering your data, make sure there is one blank space between the values in your cells, or remove spaces between numbers in Excel, you can employ the standard TRIM and SUBSTITUTE functions, use the Replace option, or run Cell Cleaner add-in that was designed to delete any extra spaces. See this video to learn the ways of removing unwanted whitespace in Excel.

How to delete spaces in Excel: video transcript

When you sort, compare, or calculate records in your tables, spaces at the beginning or at the end of your cells and extra blanks between the values become a problem. In this video we'll look at three common ways you can remove extra spaces using a special tool or formulas in Excel.

How to remove leading and trailing spaces in Excel

One of the first add-ins we created was for this very typical task: removing leading and trailing spaces. Cell Cleaner add-in makes it very easy to get rid of any unwanted characters. Here is how it works:

  • First you select the range that you want to clean up.
  • Find the Trim Spaces icon on the Ablebits Data tab, and select the option to remove leading and trailing spaces.
  • You can choose to back up the sheet as you can't cancel any changes made by add-ins, and just click Trim.

You can also use the TRIM function in Excel to do this. First you need to create a helper column that you will use for the function. We enter =TRIM, and point to our first cell with data in parenthesis.
Next you need to copy this formula down the column. Thus you'll apply it to all cells below. The quickest way to do it is point to the lower-right corner of the cell with the formula, and double-click the black plus sign that you'll see.
Now, if you want to work with the values, you can copy the results, and replace the formulas using the Paste Special option. Right-click where you want to get the values, and choose to paste special, values only. Now you can remove the column you don't need.

How to delete extra space between two words in Excel cells

If your task is a bit trickier and you want to remove extra spaces between the words, you can use Cell Cleaner to delete them. Select your data, click on the add-in's icon, and pick the option that trims blanks between words to one. Click Trim and your data will be cleaned up.

How to get rid of all spaces in Excel

When you end up with data like this, and you can't use numbers because of spaces between them, you can use one of standard means to remove all blanks.

One way is using the standard Replace feature in Excel:

  • Select your range with numbers and press Ctrl+H to open it.
  • Enter the space in the "Find what" field, and make sure there is nothing in the "Replace with" field.
  • Click "Replace all", and get the numbers you can use for your work.

The second way you can go is use the Substitute function. Add a column for the formula, and enter =SUBSTITUTE(A2," ","")
Enter A2, the first cell with the number, add a comma, then enter a space in quotes: that's the character you want to replace, comma, and enter quotes without anything inside: our substitution.

What it does, essentially, is replace all spaces in the specified cell with nothing.

Now you need to copy the formula down the column; double-click on the plus sign for the quickest way. Copy the results, and paste special, values to work with them.

Wherever you have unwanted spaces, they won't let you use the data in your calculations. All in all, you have three ways of removing blanks:

  1. You can use the Substitute function or the "Replace" dialog box in Excel to delete all blanks from the selected cells;
  2. Use the Trim function to delete leading and trailing spaces;
  3. Or run Cell Cleaner to remove any extra characters before, after, or between the words.

You can get 15 % off the add-in if you use the coupon code Preview.

Feel free to post your questions with any particular tasks, and subscribe to our channel for more tips and tricks on Microsoft Excel.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard