What is Substring in Google Sheets?
A substring in Google Sheets means a part of a string in a cell that is extracted. To master extracting substrings, you can use text functions like LEFT, RIGHT, and MID to transform how we handle data. When working with Google Sheets substrings, we can also use advanced techniques, usually with regular expressions and the extraction of specific data types. These are useful in a variety of scenarios like pulling initials from names or dates, extracting names for email IDs and so on.
For example, we have the words “Hello World” in cell A1. To extract “Hello” from the cell containing the “Hello World” text, we have to extract this substring. We can use the LEFT function to extract the left part of the string. Enter the following function in cell B1.
=LEFT(A1,5).
We are extracting the first five letters on the left side of the string in A1. The result is “Hello,” as seen below.
Key Takeaways
- We can extract substrings in Google Sheets from strings using various functions depending on our requirements.
- The LEFT function extracts a specified number of characters from the start of a string.
- The RIGHT function is used to extract characters beginning from the end of the string.
- The MID function extracts a substring from the middle of a string, starting from a specific position given by us.
- The FIND function is a case-sensitive function that returns the position of the first occurrence of a substring within a string.
- All these functions, in conjunction with SPLIT and SUBSTITUTE, help in string manipulation and extraction in an easy manner.
How to Extract Substring in Google Sheets?
A substring is a part of a main string that is located in a cell. To extract them, we use built-in functions like LEFT, RIGHT, and MID to extract substrings from a text string in a cell. Let us look at each one of them in turn.
#1 – Using the MID Function
MID extract a substring from the middle of the string.
Syntax for MID
=MID(text, start_num, num_chars)
- text: The string from which we extract the substring.
- start_num: The position to begin extraction.
- num_chars: The number of characters to extract.
#2 – Using LEFT and RIGHT Functions
LEFT: Extracts a substring from the leftmost character.
RIGHT: Extracts a substring from the right side of a string.
=LEFT(text, [num_chars])
- text: The string or cell reference that contains the text you want to extract from.
- num_chars: (Optional) The number of characters you want to extract. If omitted, it will default to 1.
Example: If you have the text “James Bond” in cell A1, and we must extract the first name, James, we use the following formula:
=LEFT(A1, 5)
=RIGHT(text, [num_chars])
- text: The string or cell reference containing the text you want to extract from.
- num_chars: (Optional) The number of characters you want to extract. If omitted, it will default to 1.
If you have the text “James Bond,” and we must extract the last name Bond, we use the following formula:
=RIGHT(A1, 4)
#3 – Using FIND with MID
We use the Google Sheets FIND function to extract a substring by finding the position of a specific character or string. This function has the following syntax:
=FIND(find_text, text, [start_num])
where
- find_text is the substring we want,
- text is the source string
- start_num specifies the character at which to begin the search. When omitted, the search starts at the beginning.
MID extract a substring from the middle of the string.
- Together they can be written as the example below.
=MID(A2, 3, FIND(” “, A2)).
Here, we start from the third character and extract characters till the space in the string.
#4 – Using SPLIT Function
We use the SPLIT function to extract parts of a string separated by a specific character, such as a hyphen or other delimiters.
Its syntax is as follows:
=SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- text: The string to split.
- delimiter: The character or set of characters to split the string.
- [split_by_each]: (Optional) A boolean value (TRUE or FALSE) that specifies whether to split by each character in the delimiter. If TRUE, it splits by every character in the delimiter. If FALSE (default), it splits only by the whole delimiter string.
- [remove_empty_text]: An optional boolean value that controls whether to remove empty text from the results. (TRUE or FALSE)
Examples
In Google Sheets, one can extract substrings using different functions depending on specific requirements. Here are some of the most used functions used in some interesting examples.
Example #1 – Using the MID Function
In this example, we have used the MID function to extract a substring from the middle of a string. Let us say we have some product codes combining name, serial number, and store code. Here, we use the MID function to extract the product code.
First, for this formula, we must identify the cell containing the string, the starting location, and the length of the substring
Step 1: Enter the data in the sheet, as shown below. It is of the form, productcode-productserialnumber-storeID.
Step 2: To extract the serial number, let us apply the following formula.
=MID(A2,6,3)
This is according to the syntax =MID(string,start_loc,length)
Here, we are extracting the serial number with 3 characters. Hence, the argument number three is 3. Also, we are extracting from the sixth position. You count the starting location as the first character that is part of the substring that you want to extract.
Press Enter.
Step 3: The product serial number has been extracted. Copy the formula to other rows to see the serial numbers of other products as well.
Example #2 – Using FIND with MID
Combining FIND and MID is helpful, especially when we have delimiters at different positions, unlike in the previous example, where they were fixed. We know that the MID function returns a Google Sheets substring from the middle of any string, and FIND() is used to return the position of a specific string in a bigger string. When the source data does not have a consistent structure, we can combine these two functions for the extraction.
Step 1: We use the following function to extract the country codes from a set of phone numbers, as seen below.
=MID(A2, 2, FIND(” “, A2) – 2)
Explanation:
- A2 is the cell containing the first phone number.
- MID extracts the substring (country code) from a string starting at a specific position.
- 2 is the starting position for the country code since every country code starts after the “+” sign; so + is the 1st position.
- FIND(” “, A2) locates the position of the first space character in the phone number which is after the country code.
- Here, -2 is used to adjust the result to get only the country code length as the FIND returns the position of the space, we subtract 2 to account for the empty space and the “+” sign).
Step 2: Press Enter.
Step 3: Drag the formula to the remaining cells in the dataset.
Example #3 – Using SPLIT Function
Using the SPLIT function, Google Sheets has a great option for extracting substrings from a string. This function is very useful for separating parts of a text, such as extracting a country code, city code, etc. Let’s use the same phone numbers as in the previous example.
SYNTAX: =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
- text: The text string to split.
- delimiter: The character (or set of characters) to split the string.
- split_by_each: It is an optional boolean value (TRUE or FALSE) indicating whether to split by each character in the delimiter.
- remove_empty_text: It specifies whether to remove empty cells from the result and is optional as well.
Step 1: We use the following function to split the phone number in A2 and get the country code in a separate cell.
=SPLIT(A1, ” “). Press Enter.
Step 2: It will split the phone number into two parts, with the country code in one part and the remainder of the phone number in another.
Drag the formula to the other cells as well.
Important Things to Note
- In addition to the above functions, we use SEARCH to return the position of a substring in a string and MID to extract substrings dynamically based on the position of a particular string.
- TEXT can extract and format parts of a date as a substring. For example: =TEXT(A1, “yyyy-mm-dd.”
Frequently Asked Questions (FAQs)
There are several string functions we can use to extract a Google Sheets substring. There are string functions:
1) LEFT(): It is used to gets characters from the left side of the string.
2) RIGHT(): This extracts characters from the right side of the string.
3) MID(): This function gets the specified number of characters from the middle of a string.
4) LEN(): It returns the number of characters in a string.
5) FIND(): It returns the position of a specific character within the string.
Step 1: If you have list of items separated by commas, like “Apple, Ball, Candle, Dog”, and you want to extract each word into a separate cell.
=SPLIT(A1, “, “)
When you must extract substrings, it requires combining multiple functions like MID and SPLIT to extract the part of the string you need. To apply a formula across multiple rows, we wrap the formula in ARRAYFORMULA to process entire columns simultaneously.
Download Template
This article must help understand Substring in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Substring in Google Sheets. We learn its syntax & how to extract substring in Google Sheets with examples & a working template. You can learn more from the following articles. –
Leave a Reply