How to Use the Substring Formula in Google Sheets – 5 Ways

Extracting a portion of a string can be done by using the substring formula in Google Sheets.

This is often done when you need to extract the first characters of a title for records purposes or extract the salutations from a list with names.

In this tutorial, I will show you how to use the Substring Formula in Google Sheets.

How to Use the Substring Formula in Google Sheets

There is no Substring function but the LEFT, MID, and RIGHT Functions are considered the Substring Formula in Google Sheets.

The most commonly used formula is the LEFT Function. To use it follow the steps below:

1. In Google Sheets, identify the string which the substring will be extracted from.
2. Decide on the number of characters to be extracted from the left.
3. Using the values above as parameters for the LEFT Function.
4. Use this formula and place it on a cell on Google Sheets: “=LEFT(string, [#_of_chars_from_left]).

Extract a Substring (Text) in Google Sheets Video Tutorial

5 Different Ways to Extract a Substring in Google Sheets

There are many uses for the substring formula in Google Sheets.

In this tutorial, I will be discussing the 5 most common ones.

1. Extracting a Substring from the Left by Using the LEFT Function

The most common and simplest way to extract substrings is by using the LEFT function. Its syntax is as follows:

LEFT(string, [#_of_chars_from_left])

The ‘string’ parameter refers to the actual text or cell_reference to the cell containing the text while the ‘[#_of_chars_from_left]’ optional parameter indicates how many characters you wish to extract starting from the leftmost character.

By default, the LEFT function will only extract the leftmost character unless you specify it to be more than one.

In the example below, we have a dataset of street addresses. Wanting to identify only the house numbers, I used the formula LEFT(A2,3) to “extract 3 characters from the left of the string in cell A2”.

Using the LEFT Function as a substring formula in Google Sheets this way makes your data cleaner.

Depending on the purpose of your spreadsheet, you may also better analyze your data.

2. Extracting a Substring from the Middle by Using the MID Function

A bit similar to the LEFT Function, MID as a substring formula in Google Sheets starts from the left as well but will skip a number of characters that you’ve specified allowing it to extract a substring at the middle of the source string.

MID(string, starting_at, extract_length )

The ‘string’ parameter refers to the actual text or cell_reference to the cell containing the text.

The ‘starting_at’ parameter indicates where you want to start the extract starting with the leftmost character with the value: ‘1’. Lastly, the extract_length parameter defines how many characters will be extracted.

Unlike the LEFT Function, none of the parameters of the MID Function is optional.

In the example below, we wanted to get a distinct section from long codes. In using the MID Function, we initiated for “a substring to be extracted from the string in cell A2, starting from its 5th character, with length 10”.

The MID Function is the best substring formula in Google Sheets if you want to extract portions of a string right from the middle part and you have definite string measurements.

3. Extracting a Substring from the Right by Using the RIGHT Function

As a substring formula in Google Sheets, the RIGHT Function is the direct opposite of the LEFT Function. Basically, as its name suggests, it extracts characters starting from the rightmost character (which is assigned with the value ‘1’).

RIGHT(string, [#_of_chars_from_right])

The ‘string’ parameter refers to the actual text or cell_reference to the cell containing the text while the ‘[#_of_chars_from_right]’ optional parameter indicates how many characters you wish to extract starting from the rightmost character.

By default, the RIGHT function will only extract the rightmost character unless you specify it does otherwise.

In the example below, we were taking care of orders to be delivered through USPS and we wanted to create a list of P.O. Box numbers as identifiers for their respective orders.

This way, we were able to cut the identifiers short making them easier to be processed. There are a lot more ways to use the substring formula in Google Sheets with the first three mentioned above as the simplest among all possible options.

4. Extracting a Substring from the Left Before a Specific Text by Using the LEFT and SEARCH Functions

To extract a substring formula in Google Sheets in reference to a specific text, we take the LEFT Function method above and we’ll just have to replace the [#_of_chars_from_left] optional parameter with the SEARCH Function.

LEFT(string, SEARCH(search_for, text_to_search, [starting_at])-1)

The SEARCH function returns the position of the “search_for” parameter in the “text_to_search” string. Optionally, you may indicate the position where the SEARCH function should start for cases of long ‘text_to_search’ with multiple instances of the ‘search_for’ text in it.

In the example below, we have a dataset containing the complete address of personnel from a certain company. The complete address consists of the street address, city, and state combined in a single line with their respective labels.

We are able to extract the street address for each line. To further clean this new set of data, we can use the RIGHT + LEN functions in addition to the current one as shown in the next method.

5. Extracting a Substring from the Right After a Specific Text by Using the RIGHT and LEN Functions

In extracting substrings of varying lengths dependent on a specific text by the left side of the source string, use the following syntax:

RIGHT(string,LEN(string)-LEN(search_for))

The ‘string’ refers to the source text or reference cell containing the source text. The ‘search_for’ parameter is the specific text that you are looking for.

As an example of this method, we are using the results of the LEFT + SEARCH functions.

Using the combination of the LEFT, SEARCH, RIGHT, and LEN functions, we are able to get the exact street addresses from the original dataset.

Various situations demanding a substring formula in Google Sheets will require different combinations of some or even all of the above. Some methods can also take advantage of the SPLIT function.

Until you master these different methods, the best way to do complex substring extractions is to do them one step at a time and just use your initial results as the reference for your next formulas.

Frequently Asked Questions about How to Use the Substring Formula in Google Sheets

Conclusion on How to Use the Substring Formula in Google Sheets

To use the substring formula in Google Sheets, identify the string which the substring will be extracted from and decide on the number of characters to be extracted from the left. For the LEFT Function, use the following formula in Google Sheets: “=LEFT(string, [#_of_chars_from_left]).