Contents
In this article, you will learn 2 quick ways to remove extra spaces between words or all spaces from Excel cells. You can use the function TRIM (TRIM) or tool Find & Replace (Find and Replace) to clean up the contents of cells in Excel.
When you paste data from an external source into an Excel sheet (plain text, numbers, etc.), you may end up with extra spaces along with important data. These can be leading and trailing spaces, multiple spaces between words, or thousands separators in numbers.
Consequently, the table looks a little untidy and becomes difficult to use. It would seem that a simple task can become difficult. For example, find a buyer named John Doe (no extra spaces between parts of the name), while in the table it is stored as “John Doe“. Or numbers that cannot be summed up, and again extra spaces are to blame.
From this article you will learn how to clear data from extra spaces:
Remove all extra spaces between words, cut off leading and trailing spaces
Suppose we have a table with two columns. In column Name the first cell contains the name John Doe, written correctly, i.e. without extra spaces. All other cells contain an entry option with extra spaces between the first and last names, as well as at the beginning and end (leading and trailing spaces). In the second column, with the title Length, shows the number of characters in each name.
Use the TRIM function to remove extra spaces
There is a function in Excel TRIM (TRIM), which is used to remove extra spaces from text. Below you will find step-by-step instructions for working with this tool:
- Add a helper column next to your data. Can you name it Trim.
- In the first cell of the auxiliary column (C2), enter the formula to remove extra spaces:
=TRIM(A2)=СЖПРОБЕЛЫ(A2)
- Copy this formula to the rest of the cells in the column. You can use the tips from the article How to insert the same formula into all selected cells at once.
- Replace the original column with the received data. To do this, select all cells of the auxiliary column and click Ctrl + Cto copy the data to the clipboard. Next, select the first cell of the original column (in our case A2), press Shift + F10 or the shortcut menu key, and then the key V (WITH).
- Delete the helper column.
Ready! We removed all extra spaces with the function TRIM (TRIM SPACES). Unfortunately, this method takes a lot of time, especially when the table is quite large.
Note: If you still see extra spaces after applying the formula, the text most likely contains non-breaking spaces. How to remove them, you can learn from this example.
Use the Find and Replace tool to remove extra spaces between words
This option requires less work, but allows you to remove only extra spaces between words. Leading and trailing spaces will also be trimmed to 1, but not completely removed.
- Select one or more columns of data in which you want to remove extra spaces between words.
- Press Ctrl + Hto open the dialog box Find & Replace (Find and replace).
- Enter a space twice in the field Find What (Find) and once in the field Replace with (Replaced by).
- Нажмите кнопку Replace all (Replace All) and then OKto close the information window that appears.
- Repeat step 4 until the message appears We couldn’t find anything to replace… (We didn’t find anything that needed to be replaced…).
Remove all spaces between numbers
Suppose you have a table with numbers in which groups of digits (thousands, millions, billions) are separated by spaces. In this case, Excel treats numbers as text and no mathematical operation can be performed.
The easiest way to get rid of extra spaces is to use the standard Excel tool – Find & Replace (Find and replace).
- Press Ctrl+Space (Space) to select all cells in the column.
- Press Ctrl + Hto open the dialog Find & Replace (Find and replace).
- In the Find What (Find) enter one space. Make sure the field Replace with (Replace with) – empty.
- Нажмите кнопку Replace all (Replace All), then OK. Voila! All spaces have been removed.
Remove all spaces using the formula
You may find it useful to use a formula to remove all spaces. To do this, you can create an auxiliary column and enter the following formula:
=SUBSTITUTE(A1," ","")
=ПОДСТАВИТЬ(A1;" ";"")
Here A1 is the first cell in a column containing numbers or words, in which all spaces must be removed.
Next, follow the same steps as in the section on removing all extra spaces between words using a formula.
