To extract a substring in Google Sheets, click on a cell, go to the Formula bar, and enter one of the following formulas: =LEFT(A1, 4), =MID(A1, 6, 9), or =RIGHT(A1, 3). Lastly, press Enter to get the result.
Scroll down to learn more about this topic and to make sure you don’t miss out on anything!
What Is a Substring?
In Google Sheets, a substring is a contiguous sequence of characters within a string of data. For example, the string “what a wonderful day” contains the substrings “what,” “a,” “wonderful,” and “day.”
How to Extract a Substring in Google Sheets: The Essentials
Extracting a substring from a larger string is a common operation in Google Sheets. There are several ways to do this. Let’s take a look at the various options we have available.
Extract Substring from the Left Side of the String
To extract text from the beginning of a string, simply do the following:
- Click on a blank cell where you want the result.
- Navigate to the Formula bar and enter the formula =LEFT(A1, 4), where A1 is the cell with the text and 4 is the number of letters you need to copy into the new cell.
- Press the Enter key on your keyboard.
Super easy!
Extract Substring from the Middle of the String
The following steps will show you how to extract text from the middle of a string. Let’s see how it works!
- Select a blank cell.
- In the Formula bar, type the formula =MID(A1, 6, 9). Here A1 is the cell with the text, 6 is the letter from which the count begins, and 9 is the number of letters you want to extract.
- Hit the Enter key.
Easy as ABC!
Extract Substring from the Right Side of the String
Next, let’s consider how to extract text from the end of a string!
- Select a blank cell where you want the result to show.
- Go to the Formula bar and enter the formula =RIGHT(A1, 3), where A1 is the cell with the text, and 3 is the number of letters you need to extract.
- Press Enter.
And there you have it!
How to Extract a Substring Using Certain Text
Learn how to extract a substring using specific text by following the formulas in this section.
Extract a Substring Before a Certain Text
If you want to extract from the beginning of a string based on a specific word, there is a way to do this. Take a look below:
- Select the cell where you want the result.
- In the Formula bar, enter the formula =LEFT(A1, SEARCH(“green”, A1)-1). A1 is the cell with text, and green is the word from which counting begins.
- Press the Enter key on your keyboard.
That’s all it takes! Here is the result.
Extract a Substring After a Certain Text or Character
Check out the following step-by-step guide to extract a substring after a specific text or character:
- Click on a blank cell.
- Go to the Formula bar and type the formula =REGEXREPLACE(A1,”(.*)green(.*)”,”$2″), where A1 contains the text and green is the word from which counting begins.
- Press the Enter key.
Voila!
How to Pull Text from Between Two Occurrences of a Character
If you need to find and extract the text from between two special characters, you can use the method below to find what you need:
- Click on a blank cell where you want the result.
- Navigate to the Formula bar and use the formula =MID(A1, SEARCH(“_”,A1) + 1, SEARCH(“_”,A1,SEARCH(“_”,A1)+1) – SEARCH(“_”,A1) – 1). A1 is the cell with your text, and “_“ are the characters between which the desired word is located.
- Hit the Enter key.
Here is the desired result!
How to Extract the Nth Word from a Text String
Take a look at the process below to see how to extract the nth word from a text string:
- Select a blank cell.
- Go to the Formula bar and enter the formula =TRIM(MID(SUBSTITUTE($A$1,” “,REPT(” “,LEN($A$1))), (B1-1)*LEN($A$1)+1, LEN($A$1))). Here A1 contains the text and B1 is the cell with the word number you need to pull (in this case, 3, or the 3rd number).
- Tap Enter.
And there is the result!
How to Extract a Substring Between Parentheses
Check out this guide on how to pull text from between parentheses in a string:
- Select the cell where you want the result.
- In the Formula bar, enter the formula =MID(A1,SEARCH(“(“,A1)+1, SEARCH(“)”,A1)-SEARCH(“(“,A1)-1). Here, A1 is the text-filled cell.
- Lastly, hit the Enter key.
Great! We have extracted the word between the parentheses.
How to Replace One Substring With Another
If you need to replace one substring with another, you can do it in just a few seconds:
- Click on a blank cell.
- Move to the Formula bar and type the formula =REPLACE(A1, FIND(“132”, A1), 3, “123”), where A1 contains text, and 132 is the text you need to replace with 123.
- Finally, press Enter.
Piece of cake!
How to Extract a Number from a String
Here are some tips that will help you extract a number from the string in case you need to do so:
Extract Number Substring from the Left of the String
It is recommended to use the formula below if the numbers are at the beginning of the cell.
- Choose the cell where you need the extracted number to go.
- In the Formula bar, type the formula =REGEXEXTRACT(A1,”^\d+”). A1 is the cell with the data.
- Press the Enter key.
Easy!
Extract Number Substring from the Right of the String
Now we will show how to extract numbers if they are located at the end of the entire text:
- Select the cell where you want the extracted number to go.
- Move to the Formula bar and enter the formula =REGEXEXTRACT(A1,”\d+”). Here A1 contains the text.
- Hit Enter.
There you go! You have done it!
Extract Substring from Anywhere in a String
If there are numbers in the middle of your text, you can use the following formula to extract them into a separate cell. Let’s take a look:
- Click on a blank cell.
- Go to the Formula bar and enter the formula =REGEXEXTRACT(A1, “\d+”), where A1 contains your text.
- Press Enter.
The process is super simple!
