Most of the Text functions in Google Sheets are easy to use except the RE2 Regular Expression Functions like REGEXEXTRACT. In this post I am explaining you the use of MID, SEARCH functions in Google Sheets. Both are very easy to learn.
Usage of MID, SEARCH Functions in Google Sheets
Using MID Function is a child’s play. I can give you a very clear picture of this Google Sheet Text function with few examples. I’m going straightaway to the tutorial. First let’s learn how to Use MID Function in Google Sheets.
How to Use MID Function in Google Sheets
MID(string, starting_at, extract_length)
The purpose of MID function is to return a certain portion or segment of a string. You can return a segment of a string from start to end.
Result = “inf”
In the above MID formula “info inspired” is the string, 1 is the starting_at and 3 is the extract_length.
The below example is enough to understand the usage of Google Sheets MID formula.
Now it’s time to jump to the next Function. Why? See the above example at row # 8 where you can see a long text and we extracted the last portion, i.e., “New Delhi”.
Here you can see that it’s time taking to find the starting_at as you want to count each characters up to New Delhi. But here we can use Google Sheets SEARCH function to find the starting_at position.
Before going to that one more thing! You can use above MID function to split numbers to digits. Below is that advanced tutorial.
How to Use SEARCH Function in Google Sheets
Google Sheets SEARCH function returns the position at which a string is first found within text.
SEARCH(search_for, text_to_search, [starting_at])
=SEARCH(“New Delhi”,”You are transferred to New Delhi”)
Here in this example, we search_for the word “New Delhi” with in the sentence or text_to_search. So it returns 24.
This number we have used in the above MID function to extract the string “New Delhi”
When you have repeated words in your sentence, you can use the SEARCH function as below.
See the below sentence.
“Moonar Tourist Destination, the best tourist destination”
The SEARCH function is not case sensitive. Here we want to find the position of string “Tourist”, but the second occurrence. How to find that using SEARCH function?
=search(“tourist”,”Moonar Tourist Destination, the best tourist destination”)
This formula will obviously return the first position of the string tourist that is 8. So use the formula as below.
=search(“tourist”,”Moonar Tourist Destination, the best tourist destination”,9)
See the last part of this formula where I’ve added # 9. That means, as per this example, putting a number above 8 will return the second occurrence of the same word.
Tricky Ways to Extract String or Text or Word from a Sentence
See the below sentence. I want to extract the word “Kochin” form it.
“Check your mail that confirming your transfer to Kochin”
You can use four different formulas to do it. I suppose the above sentence is in Cell A19.
Formula 1 using MID function.
When you know the first part of the sentence, you can use this MID formula.
Formula 2 using MID and LEN Function Combination.
=MID(A19,LEN(“Check your mail that confirming your transfer to “),7)
This is same as above. Here also you should know the first part of the sentence. But here instead of manually counting the characters, you can use LEN function inside MID.
Formula 3 using MID and SEARCH Function Combination.
This is different. This formula you can use when you know the text to extract but you are unsure about the first part of the sentence.
You can simplify this formula by using Regexextract function as below.
Formula 4 using Regexextract Function
When you want to extract any specific test from a sentence of string, the best formula is this Regexextract.
That’s all. Hope you understand the use of MID, SEARCH Functions in Google Sheets. Enjoy!