If you’re using Google Sheets and you have a column of data like the one in the image with leading and trailing spaces, it can be tricky to clean it up. There may be spaces before some of the letters or spaces after them that you want to get rid of. This tutorial will show you steps to get that done. This process can be just a few steps, but you may have some white space that is more difficult to get rid of and the later examples in this guide will show you how to deal with those.
Regular “space bar” spaces
If it’s just a matter of getting rid of spaces that are from someone hitting the space bar, then it is pretty easy. If you hit your F2 button or double left-click your mouse in one of the cells, you can see there’s a leading space because the cursor isn’t next to the first character. I you use the left or right arrow key, the cursor will get to the characters, but it starts out to separated by white space. These example images are showing both leading and trailing white space.
The four names in the example above all have regular leading and trailing spaces. These can be taken care of with the TRIM formula. That is going to be the quickest option as this is what this function is made for. Copy the TRIM formula down the column and it works…until it doesn’t.
Video explanation
When TRIM doesn’t work
The TRIM function worked on the first four examples. If that works on all of your data, great! Leave it there because it’s the easiest to use and maintain. But, once we got to Shannon Rutherford in the example above, it stops working. It’s the same TRIM function, but there’s a leading space that did not go away. What is happening is that there’s a non-breaking space. A non-breaking space is a little bit different from a “regular” space and it’s just not what the TRIM function looks for. The TRIM function is doing its job, but it thinks there is nothing to get rid of. A non-breaking space is special character, number 160 if you are keeping track.
Now we’ve added the SUBSTITUTE function. What the SUBSTITUTE function does is it looks to see if there are any character 160s, which is a non-breaking space, and, if so, changes them into a regular space. Then the TRIM comes along and gets rid of that regular space. So that’s kind of the next level. If you still have leading spaces after using SUBSTITUTE, you might just want to jump to the third level.
Add in the CLEAN function and there are three functions wrapped into a formula that should get rid of everything. In this example, it is removing the line break from Jin-Soo Kwon These three functions in combination also trim white space out of the middle so be careful. Make sure that’s what you want.