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:


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 space using TRIM in LibreOffice Calc Cell
Remove space using TRIM in LibreOffice Calc Cell

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.


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 SUBSTITUTE function in LibreOffice Calc Cell
Remove space using SUBSTITUTE function in LibreOffice Calc Cell

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. “”.

Remove space using FIND and REPLACE all dialog
Remove space using FIND and REPLACE all dialog


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.


Join our Telegram channel and stay informed on the move.

Creator of libreofficehelp.com. The aim is to help as many people as possible with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Newest Most Voted
Inline Feedbacks
View all comments

I want to remove the spaces in this cell: “+32 473 88 57 23”
Trim, Substitute, find & replace do not work.
Is there another solution?