Replace and Substitute Text in LibreOffice Calc


This article explains two essential functions of REPLACE and SUBSTITUTE in LibreOffice Calc.

Often, you need to use the SUBSTITUTE function to replace a portion of a text in a cell. It can be in a single cell or a range of cells. So, to do that, you need to know these two functions. And with these two, you can easily manipulate any strings and get the desired result in no time.

Let’s find out.

Replace and Substitute in LibreOffice Calc



SUBSTITUTE (text, search text, new text, occurrence)
  • Text: the target text where the search occurs
  • Search text: The text to search
  • New Text: The text to replace if the search is successful
  • Occurrence: The occurrence of the matched text to replace (optional)


I have a text that says, “LibreOffice is awesome”. Now I want to replace “awesome” with “great”. So, to do that, use this formula.

Basic Substitute function workings
Basic Substitute function workings

Let’s try with another example. Say we have the text “LibreOffice 1234 is great 1234, and it is cool”. What if we want to replace the “1234” occurrences with a character “-“.

So, to replace the 1st occurrence, you use the same formula but with the last parameter as 1 i.e. the occurrence of the first “1234”. This is how it works.

Second example of SUBSTITUTE
A second example of SUBSTITUTE

If you want to replace the second occurrence, choose option 2 as a final parameter. But remember, it would not replace the first one.

Using this method, you can easily remove the texts if you use an empty string (“”) as a replacement character. In fact, you can remove spaces if you can find space and replace it with an empty string.



REPLACE (text, position, length, new text)
  • Text: the target text where the search occurs
  • Position: Starting position of the search
  • Length: The length of the text to replace
  • New text: The text to replace a specified length


To demonstrate, we prepared a set of random data with a numeric part and alphabets. So, what if we want to replace all the alphabets with empty strings to get a clean set of numeric data.

To do that, we used the below formula.

Example of REPLACE in Calc
Example of REPLACE in Calc

As you can see, the function searched from the 4th position and replaced the following two characters with empty spaces, leaving you just the numbers.

One of the crucial aspects to remember is that to change a column of data, you need to analyse whether the replacement part starts with the same position for the entire data set. So, be cautious while using this in that context.


IF you need byte-level manipulation, use the REPLACEB function. It works with the byte position instead of the character position.


I hope it helps you play around and clean up your data as per your need. Drop a note down below if you need help, or it helps you.

Official reference

Join our Telegram channel and stay informed on the move.

Notify of

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

Inline Feedbacks
View all comments