Easily Remove Spaces in LibreOffice Calc

This tutorial will explain how to remove spaces in the LibreOffice Calc cell.

When you work with data received from various sources, they are not always in proper format or clean. One of the critical aspects of cleaning up data is removing spaces – whether they are leading areas or trailing ones. You would be surprised how often you face this – to remove space in LibreOffice Calc cells.

The main problem is the space itself is not visible. And that makes it difficult to remove.

Nevertheless, it is straightforward to do. Here’s how.

Remove Spaces in LibreOffice Calc Cells

I will try to explain this using some examples to help you understand.

The first method we will explain is using the TRIM method. It takes the text as input and returns it after moving the leading and trailing spaces. But remember, it will not remove the spaces between words. For that, we will use something different in the next section.

So, you have this sample data as presented below.

Remove Spaces Using TRIM Function

Now, type in the next cell (Or anywhere you want) =TRIM and mention the cell number. For example:

=TRIM(A2)

Then hit Enter. As in the result, you can see the leading and trailing spaces are removed.

Similarly, you can continue adding the function or drag the cell handle to cover as much cell you want.

But, How can you be sure that spaces are removed?

Well, you can’t see space. Then the only way to find it out is to count the characters. This is an optional step to verify whether spaces are removed.

Add a helper column and type =LEN to calculate the length of the cell text.

Here’s the example with some sample data.

Remove Spaces Using SUBSTITUTE function

If you look closely, you will notice in the above example is that the word separator, which is also space, is not removed. Because the TRIM function only removes the leading and trailing spaces.

What should you do if you want to remove every occurrence of space from the cells?

The SUBSTITUTE function helps to do that.

Syntax

SUBSTITUTE("Text"; "SearchText"; "NewText" [; Occurrence])

If we apply this function in the above example, you can see that all the spaces are removed from the cells. We find for the single space ” ” and replace them with the empty string i.e. “”. See the below image.

=SUBSTITUTE(A2," ","")

Remove Space Using Find and Replace

There is another way of removing space if you have a very large worksheet with a massive data set. It is difficult to manually enter the formula in each cell and problematic to add helper cells.

In those situations, this method is best and clean. You can use the Search and Replace dialog to find a single space and replace them with an empty string, i.e. “”.

Conclusion

I hope the above methods help you remove space in the LibreOffice Calc spreadsheet program. If you need help or further assistance, let me know in the comment box below.

Cheers.