How to Remove Spaces in Excel Formulas

Sometimes, when you work with data in Excel, you may encounter unwanted spaces in your cells. These spaces can cause problems when you try to perform calculations, sort, filter, or apply formatting. For example, if you have a cell that contains ” 100 ” with spaces before and after the number, Excel will treat it as text, not as a number. This means that you cannot use it in arithmetic operations, such as adding or subtracting.

Fortunately, Excel has some built-in functions and tools that can help you remove spaces from your cells. In this article, we will show you how to use the TRIM, SUBSTITUTE, and CLEAN functions, as well as the Find and Replace option, to get rid of spaces in Excel formulas.

The TRIM Function

The TRIM function is one of the most common and useful functions to remove spaces in Excel. It removes all leading and trailing spaces from a text string, as well as any extra spaces between words. For example, if you have a cell that contains ” John Doe “, the TRIM function will return “John Doe” without any spaces before or after the name.

The syntax of the TRIM function is:

=TRIM(text)

where text is the cell or text string that you want to remove spaces from.

To use the TRIM function, follow these steps:

1. Select a blank cell where you want to enter the formula.
2. Type =TRIM( and then select the cell that contains the text with spaces, or type the text string in quotation marks.
3. Close the parenthesis and press Enter.
4. Copy the formula to other cells if needed.

Here is an example of how to use the TRIM function to remove spaces from a column of names:

The result is:

Note that the TRIM function does not remove single spaces between words, only extra spaces. If you want to remove all spaces, including the ones between words, you can use the SUBSTITUTE function, which we will discuss later.

The SUBSTITUTE Function

The SUBSTITUTE function replaces a specific text string with another text string in a cell or text string. You can use it to remove all spaces from a cell or text string by replacing them with empty strings. For example, if you have a cell that contains “100 000”, the SUBSTITUTE function can return “100000” without any spaces.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

where text is the cell or text string that you want to replace text in, old_text is the text that you want to replace, new_text is the text that you want to replace with, and [instance_num] is an optional argument that specifies which occurrence of old_text you want to replace. If you omit this argument, the function will replace all occurrences of old_text.

To use the SUBSTITUTE function to remove all spaces from a cell or text string, follow these steps:

1. Select a blank cell where you want to enter the formula.
2. Type =SUBSTITUTE( and then select the cell that contains the text with spaces, or type the text string in quotation marks.
3. Type a comma, and then type ” ” (a space) in quotation marks as the old_text argument.
4. Type another comma, and then type “” (two quotation marks) as the new_text argument. This means that you want to replace spaces with empty strings.
5. Close the parenthesis and press Enter.
6. Copy the formula to other cells if needed.

Here is an example of how to use the SUBSTITUTE function to remove all spaces from a column of numbers:

The result is:

Note that the SUBSTITUTE function can also remove other characters from a cell or text string, such as commas, dashes, or line breaks. You just need to specify the character that you want to remove as the old_text argument.

The CLEAN Function

The CLEAN function removes non-printable characters from a cell or text string. Non-printable characters are invisible characters that are usually generated by other applications or systems, such as tabs, carriage returns, or line feeds. These characters can cause errors or unexpected results when you work with data in Excel. For example, if you have a cell that contains “John” followed by a non-printable character, Excel will not recognize it as the same as “John” without the non-printable character.

The syntax of the CLEAN function is:

=CLEAN(text)

where text is the cell or text string that you want to remove non-printable characters from.

To use the CLEAN function, follow these steps:

1. Select a blank cell where you want to enter the formula.
2. Type =CLEAN( and then select the cell that contains the text with non-printable characters, or type the text string in quotation marks.
3. Close the parenthesis and press Enter.
4. Copy the formula to other cells if needed.

Here is an example of how to use the CLEAN function to remove non-printable characters from a column of names:

The result is:

Note that the CLEAN function only removes the first 32 non-printable ASCII characters (codes 0 through 31). If your text contains other non-printable characters, such as CHAR(160), which is a non-breaking space, you can use the SUBSTITUTE function to remove them, as we discussed earlier.

The Find and Replace Option

Another way to remove spaces in Excel formulas is to use the Find and Replace option. This option allows you to search for a specific text string and replace it with another text string in a cell, a range of cells, a worksheet, or a workbook. You can use it to remove spaces by replacing them with nothing.

To use the Find and Replace option to remove spaces, follow these steps:

1. Select the cell, range of cells, worksheet, or workbook where you want to remove spaces.
2. Press Ctrl + H to open the Find and Replace dialog box.
3. In the Find what box, type a space.
4. Leave the Replace with box blank.
5. Click Options to expand the dialog box and choose the scope and settings of your search. For example, you can choose to match case, match entire cell contents, or look in formulas, values, or comments.
6. Click Replace All to replace all spaces with nothing, or click Replace to replace one space at a time.

Here is an example of how to use the Find and Replace option to remove spaces from a column of names:

The result is:

Note that the Find and Replace option can also remove other characters from your cells, such as commas, dashes, or line breaks. You just need to type the character that you want to remove in the Find what box.