/

Replace and Substitute Text in LibreOffice Calc

2194 views

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

Syntax

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)

Example

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

=SUBSTITUTE(B4,"great","awesome")
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.

=SUBSTITUTE(B6,"1234","-",1)
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

Syntax

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

Example

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.

=REPLACE(B3,4,2,"")
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.

Tip

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

Summary

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


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 and no BS. 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