In this article, you will learn how to remove spaces from a text string in Excel using different methods. You will also learn the basic theory behind each method and the advantages and disadvantages of each one. Finally, you will see a practical example of how to apply these methods to a real data set.
There are three types of spaces that you may encounter in a text string:
- Leading spaces: These are the spaces that appear before the first character of the text. For example,
" Hello"has one leading space. - Trailing spaces: These are the spaces that appear after the last character of the text. For example,
"Hello "has two trailing spaces. - In-between spaces: These are the spaces that appear between the characters of the text. For example,
"Hello World"has two in-between spaces.
To remove spaces from a text string, you need to identify which type of spaces you want to delete and use the appropriate method. There are three main methods that you can use in Excel:
- The TRIM function: This function removes all leading and trailing spaces from a text string, as well as any extra spaces between words. It leaves only one space between words. For example,
=TRIM(" Hello World ")returns"Hello World". - The SUBSTITUTE function: This function replaces a specific character or text with another character or text in a text string. You can use it to remove all spaces from a text string by replacing the space character with an empty text. For example,
=SUBSTITUTE(" Hello World "," ","")returns"HelloWorld". - The Find and Replace option: This option allows you to find and replace any text or character in a range of cells. You can use it to remove all spaces from a text string by finding the space character and replacing it with nothing. For example, if you select the cell that contains
" Hello World "and use the Find and Replace option to find" "and replace with"", you will get"HelloWorld".
Procedures
To use any of the methods described above, you need to follow these steps:
- Step 1: Select the cell or range of cells that contains the text string that you want to remove spaces from.
- Step 2: Depending on the method that you choose, do one of the following:
- If you want to use the TRIM function, enter the formula
=TRIM(A1)in another cell, where A1 is the cell that contains the text string. You can also drag the formula down or across to apply it to multiple cells. - If you want to use the SUBSTITUTE function, enter the formula
=SUBSTITUTE(A1," ","")in another cell, where A1 is the cell that contains the text string. You can also drag the formula down or across to apply it to multiple cells. - If you want to use the Find and Replace option, go to the Home tab, click on Find & Select, and choose Replace. In the Find what box, enter a space character. In the Replace with box, leave it blank. Click on Replace All to remove all spaces from the selected cells.
- If you want to use the TRIM function, enter the formula
- Step 3: Copy the results and paste them as values in the original cells or in another location. This will overwrite the original text strings with the trimmed ones.
Example
To illustrate how to use these methods, let’s look at an example of a data set that contains some text strings with spaces. The data set is shown in the table below:
Table
| Name | Length |
|---|---|
| John Doe | 8 |
| Jane Smith | 10 |
| Bob Jones | 9 |
| Alice Lee | 9 |
| Tom Green | 9 |
| Mary Brown | 10 |
| Peter Parker | 13 |
| Clark Kent | 11 |
| Bruce Wayne | 11 |
| Diana Prince | 12 |
As you can see, some of the names have leading, trailing, or in-between spaces that make them look inconsistent and inaccurate. To remove these spaces, we can use any of the methods described above. For example, if we use the TRIM function, we can enter the formula =TRIM(A2) in cell B2 and drag it down to cell B11. The result is shown in the table below:
Table
| Name | Length | Trimmed Name | Trimmed Length |
|---|---|---|---|
| John Doe | 8 | John Doe | 8 |
| Jane Smith | 10 | Jane Smith | 10 |
| Bob Jones | 9 | Bob Jones | 9 |
| Alice Lee | 9 | Alice Lee | 9 |
| Tom Green | 9 | Tom Green | 9 |
| Mary Brown | 10 | Mary Brown | 10 |
| Peter Parker | 13 | Peter Parker | 12 |
| Clark Kent | 11 | Clark Kent | 10 |
| Bruce Wayne | 11 | Bruce Wayne | 11 |
| Diana Prince | 12 | Diana Prince | 12 |
As you can see, the TRIM function removed all the leading, trailing, and extra in-between spaces from the names, and left only one space between the first and last names. The length column shows the number of characters in each name before and after trimming. You can see that some of the names have different lengths after trimming, which means that they had extra spaces that were not visible.
Other Approaches
Besides the methods described above, there are some other approaches that you can use to remove spaces from a text string in Excel. Here are some of them:
- The CLEAN function: This function removes all non-printing characters from a text string, such as line breaks, tabs, or carriage returns. These characters may not be visible, but they can cause problems when you work with the data. For example,
=CLEAN("Hello"&CHAR(10)&"World")returns"HelloWorld". - The TEXTJOIN function: This function combines the text from multiple cells or ranges into one text string, with a specified delimiter. You can use it to remove all spaces from a text string by using an empty text as the delimiter. For example,
=TEXTJOIN("",TRUE,A1:A10)returns"JohnDoeJaneSmithBobJonesAliceLeeTomGreenMaryBrownPeterParkerClarkKentBruceWayneDianaPrince". - The CONCATENATE function: This function joins two or more text strings into one text string. You can use it to remove all spaces from a text string by concatenating the text without spaces. For example,
=CONCATENATE("Hello","World")returns"HelloWorld".
