Easily Remove Spaces in LibreOffice Calc

4301 views

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

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 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

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.


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Join our Telegram channel and stay informed on the move.


arindam

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

Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x