If you have been a Google Sheets user for a while now, you may have noticed that Excel and Google Sheets provide a variety of features that do a similar job. However, whilst Excel extracts substrings, Google spreadsheet substring extraction does not have a specific function targeting it. But, on a brighter note, Google Sheets provides many different functions that we can customize to get the job done.
Table of Contents
What is a Substring, and Why Extract It?
A substring is simply a section of a string. A string is a collection of characters within a cell. Usually, when we are talking about strings we are referring to a text string like “Hello World.” But they can be numerical too. Some substrings from that example could be:
- Hello
- World
- H
- Ell W
Or any other combination of text within that entire string. Extracting substrings can be useful for finding matching data as well as simplifying worksheets.
Overview of the Methods
As mentioned before, users can use a combination of Google Sheet string functions to extract numbers from a string in Google Sheets since it does not have a direct feature. Here are the three best methods that you can use to extract substring in Google Sheets.
- LEFT
- RIGHT
- MID
These functions can be used to extract a number of characters from a Google Sheets string. You can use them to extract numbers, words, or a sequence of characters from a specific cell.
They work depending on the placement you choose and support numbers, text, spaces, and special characters. In terms of operation, the LEFT function starts extracting characters from the left, and you can choose the number of characters to extract.
The RIGHT function does the same, but instead of starting from the left, it starts from the right.
The MID function has a specific syntax attribute that lets you choose the starting point (by specifying the number of characters to skip) and the number of characters to select from that starting point.
The Syntax for Each Method:
LEFT(string,number_of_characters)
RIGHT(string,number_of_characters)
MID(string, starting_point,number_of_characters)
The text in the syntax explains the values to be placed there. While using the functions, you must remember that you’ll also have to account for spaces and any special characters since these functions will also consider spaces and special characters.
You can use these functions to extract Google Sheet substring from multiple cells. For that, you have to use the concatenate function. You can use the concatenate function to extract multiple substrings from the same cell or extract substrings from multiple cells together.
Here’s an example of the syntax:
CONCATENATE(LEFT(string,number_of_characters),MID(string,starting_point,number_of_characters))
Related: How to Remove the First Digit From a Cell’s Data
Guide for Google Spreadsheet Substring Extraction
Below is a Google Sheet with a sentence in cell A2 and a combination of text and numbers in cells A3 and A4.
Here’s a detailed guide on using each method described above to extract substrings from this data:
LEFT FUNCTION
In the screenshot above, I have selected A2 as my cell and entered 5 as the number of characters. By doing so, Google Sheets has extracted five characters starting from the left side, which forms the word “Hello”.
RIGHT FUNCTION
In the screenshot above, I have again selected A2 as my cell and entered 14 as the number of characters. By doing so, it extracted fourteen characters starting from the right side, which forms the phrase “Google Sheets!”. As you can see, this function has selected the characters starting from the right, but it has also included an exclamation mark at the end of Google Sheets. To avoid this, you can use the MID function (explained below).
MID FUNCTION
In the screenshot above, I have selected A2 as my cell and entered 26 as my starting point and 13 as the number of characters. By doing so, Google Sheets has skipped the first 25 characters and extracted 13 characters starting from the 26th character, which forms the phrase “Google Sheets”.
Here’s another example of the MID function used in a single line of text instead of a sentence:
CONCATENATE FUNCTION
In the screenshot above, I used the concatenate function and the MID function to extract a sequence of text from the mixed numbers and text in cells A3 and A4.
This function is handy since it allows the extraction of substrings from multiple cells as well as multiple substrings from the same cell. In addition, you can use the LEFT and RIGHT functions along with the CONCATENATE function as well.
Additional Methods of Extracting
So, now we’ve been through the Google Sheets extract substring from a string functionality. Another function, the REGEX function, can help you organize or evaluate your data.
The Google Sheets REGEX function has three types: REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE. Each type of this function performs its own duty.
- REGEXMATCH – This type of function helps you find the pattern in the text. Similar to IF Contains in Google Sheets.
- REGEXEXTRACT – Aids in extracting the text that matches the pattern.
- REGEXREPLACE – this function, somewhat like a few other Google Sheets functions, helps you replace the text that matches the pattern.
The REGEX function is a little different to the string functions in Google Sheets we discussed in this article. Although it can be challenging to learn, once you start using it frequently, you’ll find it easier and more manageable. You can learn more about these functions in our dedicated REGEX Google Sheets Guide.
Frequently Asked Questions
How Do I Find a Substring in a String in Google Sheets?
If you need to find a Google Sheets substring, you can use multiple methods along with the string functions Google Sheets provides. The list of which includes adding the ISNUMBER Function, using the MID function, or using the RIGHT function.
How Do I Extract Specific Text From a Cell in Google Sheets?
You can use LEFT + SEARCH in Google Sheets to extract text from a string or to extract data that comes before a specific text.
LEFT is used to return a specific number of characters from the leftmost cell’s beginning.
SEARCH looks for specific characters/strings and determines their position.
What if you only want to extract numbers and don’t care about their position or what comes before or after them? Masks, also known as regular expressions, can be useful. Similarly, you can only extract alphabetical data from Google Sheets cells. The contraction for the regular expression that represents text is called alpha.
If you want an easy formula-free way to extract various types of data, you can find add-ons from Google. The add-ons usually have the tools you need.
How Do I Pull a Text String From a Cell?
You can use an Add-on like Google Sheets Power Tools to extract data from a specific cell, all you have to do is just select a column, or a range of cells. Then you can start the add-on, run Power Tools from the Google Sheets menu: Extensions > Power Tools > Start.
After that, go to the Text group, and then Find and click the Extract icon.
Conclusion
Now that we have covered everything you need to know about Google Spreadsheet substring extraction, I hope it gave you a clear image of how this Google Sheets substring feature works. So, although Google Sheets not providing a specific feature for this purpose can be a discouraging start, on the brighter side, we still have a way to navigate this by using a mixture of different Google Sheets features. Hope you find the information in this article valuable. See you next time!
