/

Top Ways to Find Substring in LibreOffice Calc

1211 views

Here are some of the ways you can find substring in LibreOffice Calc cells.

How to Find Substring in LibreOffice Calc cell

First and foremost, no substring function is present in the LibreOffice Calc cell.

That means, you have to use the following functions (or combinations of them) while doing your work.

MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM

In this article, I will explain some examples using the above functions. That would help you to understand you can find substring in LibreOffice Calc cell values.

Using MID Function

In the following example, there are three sentences. Each has a number enclosed by parenthesis. How can you extract the number only and put it on another cell?

I will use the MID function in this example. Here’s its syntax.

MID(search_text, start_number, length)

search_text: the cell value where you want to search. This can be a value or cell reference
start_number: from which number of character, substring will be returned. This must be a whole number.
length: the size of the substring to be returned. This must be a whole number.

To do that, I have used the below formula.

=MID(A1,FIND("(",A1)+1,2)

Explanation: Search will happen on the value of A1, and then I will find out the position of “(” – first parenthesis. That would be the starting position+1 of the substring. And the length to be returned is 2.

Here’s the result in this image.

substring example 1 using MID
substring example 1 using MID

But, there’s a problem in the 3rd row which returned the closing parenthesis. Because we have supplied the length as two and there is only one number.

How to fix this? Let’s modify the formula as below.

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
substring example 2 using MID
substring example 2 using MID

Explanation: The first two part of the formula is correct. The length formula is different here. I am finding the position of closing parenthesis “)” and then subtracting it from the position of “(“, by reducing one number. This eventually gives us the desired result.

Let’s see a complex example.

Using SUBSTITUTE and MID together

Here’s a sample of the data I have. It contains a mix of words with email addresses between them. How can you extract the email address only? Note that the length of the texts and email addresses are different. Hence you can not use the text to columns.

Sample data with email addresses
Sample data with email addresses

As a first step, I will separate the email address portion by adding many spaces, such as 50 spaces. To do that, I will use the SUBSTITUTE function to replace one single space ” ” with 50 spaces by using the REPT function.

=SUBSTITUTE(A1," ", REPT(" ",50))

And here’s the result if I apply this to the first cell data, i.e. A1. As you can see, the email address is now separated from the words. Each single space is replaced by 50 spaces.

Separating email address with spaces
Separating email addresses with spaces

The next step would be to remove all the words and keep only the email address with spaces. Since I have added 50 spaces (I know already that there are 50 spaces before and after the email addresses), it’s time to find the “@” using the MID function explained above.

The trick here is to approximate the spaces, excluding the words.

=MID(A2,FIND("@",A2)-25,75)
Only email address with spaces
Only email addresses with spaces

As you can see, the email address is separated from the words. But it had spaces. All you need to do is use the TRIM function to remove the spaces.

=TRIM(A3)
Only address present
Only address present

By combining all the steps, the final formula becomes this.

=TRIM(MID(SUBSTITUTE(A1," ", REPT(" ",50)),FIND("@",SUBSTITUTE(A1," ", REPT(" ",50)))-25,75))

And here’s the final result. It might look complex, but it is not – if you think about it.

Final result of a complex substring extraction in LibreOffice Calc
Final result of a complex substring extraction in LibreOffice Calc

Download the sample example spreadsheet here.

Wrapping Up

I have shown you how to find substring in LibreOffice Calc cells with various methods. It includes MID, LEFT, RIGHT and other functions. I hope this helps you understand its basics, which you can apply to your use case.

Do let me know in the comment box if you have any questions.


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