The easiest way to remove spaces, remove stray characters, and fix other OCR, transcription, and data entry errors is with the free Google Sheets add-on Paperpusher. But, if you’d rather not download it, here’s how to remove spaces in Google Sheets without Paperpusher.
I now present to you one of the most frustrating errors when comparing values in Google Sheets:
|Are they going to the party?||People||People who have responded yes|
I’m certain I have my whole table set up perfectly. In cells A2 and A3, I’ve set up my match formula as follows:
This says IF Google manages to match the value in B2, “Jeff”, with the values in C2:C3 “Jeff” “Diane”, then it should write yes in cell A2. Otherwise, it’ll write #N/A. It looks perfect, right? And it works fine with Diane, in cell B3. But for some reason it doesn’t work with Jeff in cell B2.
Can you see why? It’s because there’s a space in front of Jeff in B2. So, Google isn’t comparing “Jeff” to “Jeff”, it’s comparing ” Jeff” to “Jeff”. It makes no difference to a person, but to a computer, they’re two different things.
So how can I get rid of that space? If there’s just one cell with a space, and I know where it is, I can just click in the cell and backspace. But what if there’s a bunch of cells with spaces, or I’m not sure if any of the cells actually have spaces? Then, I can once again use regular expressions to remove any spaces that might exist.
So, I start with my list of values that might need spaces removed, and I add an extra column next to each:
|People||People with spaces removed||People who have responded yes||Yes with spaces removed|
In cell B2, I type in the following formula:
This is a complicated formula, so let’s break it down. Using a regular expression, I look inside cell A2 with REGEXREPLACE , I extract a space ” ” from cell A2 with REGEXEXTRACT, and I replace the space with nothing “”.
This gives me the cell without a space, so I just get “Jeff” in B2. Unfortunately, this also gives me #N/A next to “Diane”, once I drag it down, because “Diane” has no space. Let’s get fancy and avoid that possibility with an IF statement in cell B3.
=IF(REGEXMATCH(A3,” “), REGEXREPLACE(A3,REGEXEXTRACT(A3,” “),“”), A3)
Whew! Here, I look inside cell A3, “Diane”. IF I can match a space ” ” inside cell A3, I carry out the REGEXREPLACE from before. If I can’t, I just retype cell A3. This formula will then work for all cells, whether they have spaces or not, and will give me a final value of the name without the space.
Again, the easiest way to remove spaces, remove stray characters, and fix other OCR, transcription, and data entry errors is with the free Google Sheets add-on Paperpusher. But, if you’d rather not download it, this was how to remove spaces from cells in Google Sheets using regular expressions.