Sometimes when copying data, you may find some annoying spaces either before, within or after the group of texts in each cell. Today’s post is dedicated to teaching you quick and easy ways to remove such spaces. Let’s begin
Using Trim Function to remove Spaces
The excel trim function is very handy in removing leading spaces, extra spaces and trailing spaces. All you need to do is to use the =Trim(text) function with the text in the formula referring to the cell with the spaces. in our example, this is cell A1.
Trim function to remove only leading spaces
You can limit the excel trim function to remove only leading spaces with a combination of other functions to produce a more complex function. In our example for this section, we have a list of states in the US with their zip codes, with a double space separating the zip code and the name of the state. However, there are irregular leading spaces just before each states zip code and we do not need those leading spaces.
Let’s start with trimming the lead spaces in cell A2. To do this, we use the formula =MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))
The FIND(MID(TRIM(A2),1,1),A2) calculates the first text character in the string, and this feeds into the first MID function to represent the start number, whilst the LEN(A2) function returns the number of characters starting at the position of the first text character.
Using Substitute Function to remove all Spaces
Let’s assume we want all spaces removed in our previous example, what do we do? Simple, we just use a substitute function and we are done.
By following the prompt in excel when you type in the substitute formula you see: =SUBSTITUTE(text, old_text, new_text [instance_num]). The Old text has two double quotations with space between whilst the new text has same double quotations but without spaces as you are directing excel to remove all spaces in the new text.
Remove Non-Printable Characters from Excel
The Clean function can be used to remove non printable characters from a text. Our example for this section is a list of some artistes we plan on inviting to our party. Our database got a little messed up and we have some characters at the beginning and end of each artiste’s name. How do we clean it up? (get it). Easy, just use the Clean Function
Now let’s assume that we have the same data set, but there are irregular leading spaces, extra spaces of trailing spaces, what do we do? Simple, combine the Trim function and clean function as seen in the screenshot below:
