Are you tired of struggling with complicated text functions in Google Sheets? Look no further! In this article, we will dive into the MID and SEARCH functions and show you how easy they are to use. Say goodbye to confusion and hello to efficient data manipulation!
Understanding the MID Function
The MID function in Google Sheets allows you to extract a specific portion or segment of a string. It’s as simple as that! Let’s break it down with an example:
Syntax:
MID(string, starting_at, extract_length)Let’s say we have the string “info inspired” and we want to extract the first three characters. The formula would look like this:
=MID("info inspired",1,3)And the result would be “inf”.
See how easy it is? The MID function is a powerful tool that can also be used to split numbers into digits. With just a little practice, you’ll be a pro in no time!
Simplifying with the SEARCH Function
While the MID function is great, sometimes finding the starting position can be time-consuming. That’s where the SEARCH function comes in handy. This function helps you locate the position of a specific string within a larger text.
Syntax:
SEARCH(search_for, text_to_search, [starting_at])For example, let’s say we want to find the position of the phrase “New Delhi” within the sentence “You are transferred to New Delhi”. We can use the SEARCH function like this:
=SEARCH("New Delhi","You are transferred to New Delhi")This would return the number 24, which we can then use in the MID function to extract the string “New Delhi”.
The SEARCH function is not case sensitive, so you can easily find repeated words in your sentence. For example, if you want to find the second occurrence of the word “tourist” in the sentence “Moonar Tourist Destination, the best tourist destination”, you can use the formula:
=SEARCH("tourist","Moonar Tourist Destination, the best tourist destination",9)By adding the number 9, the formula will return the position of the second occurrence of the word “tourist”.
Extracting Text with Ease
Sometimes, you need to extract specific words or phrases from a sentence or string. There are multiple ways to do this, depending on your needs. Let’s take a look at four different formulas you can use to extract the word “Kochin” from the sentence “Check your mail confirming your transfer to Kochin”:
Formula 1 using MID function:
=MID(A19,50,7)This formula is useful when you know the first part of the sentence.
Formula 2 using MID and LEN Function Combination:
=MID(A19,LEN("Check your mail confirming your transfer to "),7)Similar to the first formula, this one allows you to avoid manually counting the characters by using the LEN function inside the MID function.
Formula 3 using MID and SEARCH Function Combination:
=MID(A19,SEARCH("KOCHIN",A19),7)If you know the text to extract but are unsure about the first part of the sentence, this formula is for you.
Formula 4 using Regexextract Function:
=REGEXEXTRACT(A19, "Kochin")When you want to extract any specific text from a sentence or string, the powerful Regexextract function is your best friend.
Now that you have mastered the MID and SEARCH functions, you can tackle any text manipulation task with confidence. Say goodbye to spreadsheet headaches and hello to efficient data analysis!
For more helpful tips and tricks, visit Crawlan.com. Happy crunching!
