3 Cách xóa khoảng trắng trong excel

Trim blanks between words to 1, remove trailing / leading spaces

For example, you have a table with 2 columns. In the column Name, the first cell contains "John Doe" written correctly without excess spaces. All other cells have extra blanks between the first and the last names. At the same time these cells have irrelevant blanks before and after the full names known as leading and trailing spaces. The second column is called Length and shows the number of symbols in each name:

Use the Trim formula to remove extra spaces

Excel has the Trim formula to use for deleting extra spaces from text. Below you can find the steps showing how to use this option:

1. Add the helper column to the end of your data. You can name it "Trim".

2. In the first cell of the helper column (C2), enter the formula to trim excess spaces =TRIM(A2)

3. Copy the formula across the other cells in the column. Feel free to use some tips from Enter the same formula into all selected cells at a time.

4. Replace the original column with the one that has the cleaned data. Select all cells in the helper column and press Ctrl + C to copy data to clipboard.

Now pick the first cell in the original column and press Shift + F10 or the menu button  . Then just press V.

5. Remove the helper column.

That's it! We deleted all excess blanks with the help of the formula trim(). Unfortunately, it's a bit time-consuming, especially if your spreadsheet is rather big.

Note: If after using the formula you still see extra spaces (the last cell on the screenshot), please have a look at If the TRIM Function Doesn't Work

Using Find & Replace to remove extra spaces between words

This option needs fewer steps, but allows only deleting excess spaces between words. Leading and trailing spaces will also be trimmed to 1, but will not be removed.

1. Select one or several columns with the data to delete spaces between words.

2. Press Ctrl + H to get the "Find and Replace" dialog box .

3. Press the Space bar twice in the Find What field and once in Replace With

4. Click on the "Replace all" button, and then press Ok to close Excel confirmation dialog.

5. Repeat step 4 until you see the message "We couldn't find anything to replace." :)

3 clicks to neat data with Cell Cleaner add-in

If you often import data to Excel from external sources and spend much time polishing up your tables, check out Cell Cleaner add-in for Excel.

This helpful tool will clean data imported from web or any other external source. It removes leading and trailing spaces, excess blanks between words, non-breaking spaces, line breaks, non-printing symbols and other unwanted characters. This add-in also converts words to UPPER, lower or Proper Case. And if you need to change text numbers back to number format and delete apostrophes, this will not be a problem.

0. Download and install Cell Cleaner add-in for Excel.

1. Select the range in your table where you want to remove excess spaces. For new tables I usually press Ctrl + A to process all columns in a go.

2. Go to Ablebits Data tab and click on the Trim Spaces icon

3. The Cell Cleaner pane will open on the left side of your worksheet. Just select the needed checkboxes, click the Trim button and enjoy your perfectly cleaned table.

Isn't it faster than with the two previous tips? If you always deal with data processing, this tool will save you hours of precious time.

Remove all spaces between numbers

Suppose, you have a workbook with numbers where the digits (thousands, millions, billions) are separated with spaces. Thus Excel sees numbers as text and no math operation can be performed.

The easiest way to get rid of excess spaces is using the standard Excel Find & Replace option:

• Press Ctrl + Space to select all cells in a column.
• Press Ctrl + H to open the "Find & Replace" dialog box.
• Press Space bar in the Find What field and make sure the "Replace with" field is empty.
• Click on the "Replace all" button, and then press Ok. Voila! All spaces are removed.
  

Using formula to remove all spaces

You may need to delete all blanks, like in a formula chain. To do this, you can create a helper column and enter the formula: =SUBSTITUTE(A1," ","")

Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.

Then follow the steps from the part using formula to remove extra spaces between words to 1