How to Remove Spaces in Excel | Remove Special Characters | Text Formulas

by

In this tutorial, we will be discussing how to clean a string of text in Excel using a combination of functions. The functions we will be using are SUBSTITUTE, CLEAN, TRIM and CODE. These functions work together to remove spaces, special characters, and any non-printable characters from a given string of text.

Removing spaces

Removing spaces using the TRIM function Let’s say you have a string of words, “Excel Tutorials”, and you want to remove the unnecessary extra space in between “Excel” and “Tutorials”. To do this, we will use the TRIM function. This function will remove all the spaces in the string of text except for single spaces in between words. Here is a step-by-step guide to removing spaces using the TRIM function:

  1. Select the cell or cells that will contain the text you want to remove spaces from.
  2. In the formula bar, type the function =TRIM(A1) and press enter, where A1 is the cell reference of the cell you want to clean. This will remove any spaces before or after the text.

Restriction on the TRIM Function Since the TRIM function is limited to removing all spaces until each set of character(s) is separated by a single space, combining a word that is separated by a space will not be effective in combining them. For example, as shown below,

Removing spaces using the SUBSTITUTE Function. To effectively combine a word separated by spaces, the SUBSTITUTE function is used. This function will replace any specific character(s) within the selected string of text with the desired character(s). Here is a step-by-step guide to removing spaces using the SUBSTITUTE function:

  1. Select the cell or cells that will contain the text you want to remove spaces from.
  2. Enter the function =SUBSTITUTE(A2,” “,””) in the formula bar and press enter. This will replace the space between the words with a blank () character.

Removing Special Characters

Removing Non–printable Characters using the CLEAN Function. Let’s say you have a string of words with characters that Excel can’t display and you want to remove them. To do this, we can use the CLEAN function. The CLEAN function will remove any non-printable character. Here is a step-by-step guide to removing non-printable characters from a string of text.

  1. Select the cell or cells that will contain the text you want to remove spaces from.
  2. In the formula bar, type the function =CLEAN(A3) and press enter, where A3 is the cell reference of the cell you want to clean. This will remove any unrecognized characters inside the text.

Note: In the example, since Excel can display the character “ª”, it has not been removed.

Removing Special Characters. In the previous example, we want to remove the special character “ª”. To do this, we will be using a combination of CODE, CHAR, and SUBSTITUTE functions. Here’s a step-by-step guide to removing special characters:

  1. First, we have to figure out the code of the special character. Simply type in =CODE(ª) into a cell, and we will have the ASCII Code that will help us in removing the character.
  2. Using the code, we can then substitute it for a blank character. We can then type in =SUBSTITUTE(B3,CHAR(170),””). This will tell Excel to replace the character code 170 (ª) with a blank in the cell B3.

Removing Line Breaks

Removing line breaks using CLEAN. In this example, we have a word that is separated by line breaks. We want to remove these line breaks in order to form the word. To do this, we will use the CLEAN function. Simply select the cell where you want your clean text to appear and enter =CLEAN(A5). This tells Excel to remove all line breaks in A5.

Replacing line breaks using SUBSTITUTE. In the same example, we want to add a comma in between each line break. To do this, we can use the SUBSTITUTE function and the character code for the line break. The character code for a line break is 10, so we can simply use the formula: =SUBSTITUTE(A5,CHAR(10),”,”). This tells Excel to replace the line breaks with a comma.

Conclusion

Cleaning data in Excel can be a tedious task, but by using a combination of functions such as TRIM, SUBSTITUTE, CLEAN, CODE, and CHAR, it can be made more efficient. These functions can be used together to remove spaces, special characters, and any non-printable characters from a given string of text. By following the step-by-step guides provided in this tutorial, you should now have a better understanding of how to use these functions to clean your data.

Related Posts: