How to Extract a Google Sheets Substring in 2024
Do you want to learn how to extract substrings from text in Google Sheets?
Then, this step-by-step guide is for you. We'll cover everything you need to know about substring extraction in Google Sheets.
Whether dealing with names, dates, or any other text data, you'll learn how to pull out the information you need using various functions.
Understanding Substrings in Google Sheet
To understand substrings, you need to know the meaning of string — a string is a sequence of characters. It can include letters, numbers, symbols, and spaces. In most programming contexts, a string is treated as a single unit of text data.
A substring is a fragment of text extracted from a larger string.
Working with substrings in Google Sheets is an important skill for efficient data manipulation and analysis. By extracting specific parts of text, you can separate important information.
For example, you might need to extract:
Product codes from longer item descriptions, e.g. First names from full names, e.g. John from John Doe, Alberto from Alberto Mannassero.
Area codes from phone numbers, e.g. +234 from +234-xxx-xxx.
Usernames from email addresses, e.g. Dave from Dave.xxx.@gmail.com.
Google Sheets offers several built-in functions for substring extraction, namely:
LEFT: Extracts a specified number of characters from the start of a string.
RIGHT: Extracts a specified number of characters from the end of a string.
MID: Extracts a specified number of characters from any position within a string.
REGEXTRACT: Extracts a portion of text that matches a specified regular expression pattern.
The spreadsheet powered by AI
Access the power of AI to extract, clean up, and transform data. Build better spreadsheets, faster.
Try Rows for FreeStep-by-step guide on how to extract substring using LEFT, MID, RIGHT and REGEX
These functions are essential and straightforward to use. Let's start with the LEFT function:
How to use the LEFT function to extract substrings
The LEFT function extracts a specified number of characters from the start of a text or word string
The formula for substring extraction with the LEFT function is —
=LEFT(text, [num_characters])
Where string is the text you want to extract from
And [num_chars] is the number of characters you want to extract. This parameter is optional; if omitted, the function will return the string's first character.
Step 1: Prepare your data in Google Sheets
My data looks like this:
Step 2: Type in the syntax of the LEFT function on a blank space cell you wish to extract the substring.
If you noticed, I used A2 instead of typing out the whole string. Look for the numbered cell in the row of each column. For this example, the string is in the second cell of the first column (A).
Once done, click the enter tab on your keyboard and boom!
Here's what I have:
Note 💡: Google Sheets offers you formula suggestions for subsequent extractions. This makes it easier for you; however, you can always edit to your satisfaction.
How to use the RIGHT function to extract substrings
The RIGHT function extracts a specified number of characters from the end of a string. The syntax for the `RIGHT` function is as follows:
=RIGHT(text, [num_characters])
Open your spreadsheet. Go to the blank space you want the substring extracted and type in =RIGHT(text, [num_characters])
For this example, I used A2 and six characters to extract Amazon (Amazon is in row 2 or column A, and it has six characters)
Once done, click enter. The goal of this method is to extract characters at the end of a string — whether a sentence, a letter, numbers, e.t.c
How to use the MID function to extract substrings
In Google Sheets, the MID function specifies the starting position and the length of the substring to extract.
Here's the formula:
=MID(string, starting_at, extract_length)
For the example above, I used the 15th character of the string as the starting point for removing the five characters.
The results below for the two strings:
How to use the REGEXTRACT function to extract a substring
The REGEXEXTRACT function extracts a substring from a text string based on a specified regular expression pattern.
The syntax for REGEXTRACT is:
=REGEXEXTRACT(text, regular_expression)
In the `REGEXTRACT` function, the `regular_expression` is a pattern that defines a sequence of characters to search for within a given text. The `REGEXTRACT` function uses this pattern to locate and extract a portion of the text that matches the specified pattern.
Prepare your data and type in the syntax for the REGEXTRACT function:
=REGEXTRACT(text, regular_expression)
Press Enter to execute the function, returning the first match found for your regex pattern.
Google Sheets supports RE2 regular expressions, allowing for powerful data extraction and manipulation.
For example;
The pattern `\(([A-Za-z]+)\)` returns any content between brackets, such as extracting "example" from, "(example)".
The pattern `[0-9]+` returns any sequence of digits, like "123" from "abc123def".
Similarly, the pattern `[a-f]` matches any single letter from a to f.
To use these patterns with the `REGEXTRACT` function in Google Sheets, you can follow these steps:
First, identify the pattern you need based on the data you want to extract. For instance, use `\(([A-Za-z]+)\)` to extract text within parentheses.
Then, apply the `REGEXTRACT` function to your data. For example, `=REGEXTRACT(A1, "\(([A-Za-z]+)\)")` will extract the text within brackets in cell A1.
Compared to LEFT, RIGHT, and MID functions, REGEXTRACT is more complex due to its syntax. Additionally, it requires you to be familiar with the RE2 library.
How to Extract a Substring in Rows
Rows is a comprehensive spreadsheet for modern teams that offers a tidier UI, automated data ingestion capabilities, and native AI features capabilities (AI Analyst, AI-generated subtitles, native AI functions) that make reporting more accessible and faster.
With Rows, you can extract substrings with LEFT/RIGHT and/or FIND and/or LEN functions.
But most importantly, you can leverage OpenAI to simplify the entire job and solve the two issues encountered with Google Sheets, which are:
With Google Sheets, recalling syntax is hard —> but you can use natural language with Rows.
With Google Sheets, you cannot extract concepts but only portions of text, but with Rows, you can extract everything from any data structure.
So, what features differentiates Rows when it comes to extracting substrings?
EXTRACT_OPENAI
Rows leverages GPT-4o, which is fed by natural language, to perform a wide range of data mining tasks.
There are several ways to use OpenAI for data extraction.
First you can use it to extract portions of text (substrings or chars) from basically any data structure, such as strings, JSONs and Arrays. See the examples below:
From a string:
From a JSON:
From an Array:
Second you can use it to extract concepts, such as:
Critical insights from surveys: Analyze your customers' responses to open-ended questions to get straight to the point.
Feature requests from users' feedback: Quickly discover your customers' expectations by extracting what they would love to see in your product.
Contact details from emails: Extract email addresses or phone numbers from your clients' replies.
Below is the Syntax and its parameters:
EXTRACT_OPENAI(element, text, [temperature], [max_tokens], [model])
Element: The portion of text you want to extract. Examples include feature requests, email addresses or everything before the $.
Text: The text from which you want to extract the element.
Temperature [optional]: The sampling temperature is between 0 and 2. Use 2 for creative applications and 0 for well-defined answers. By default, it is 0. For example: "0.4".
[optional] max_tokens: The maximum number of tokens to generate in the completion. For example: "230".
[optional] model: The model to use. By default, it uses "gpt-4o". For example: "ada".
Want to learn how our AI-powered features work? Check out Rows AI.
Now, let's get to business 🚀
How to extract anything with EXTRACT_OPENAI
Step 1: Connect the OpenAI Integration.
To connect the integration, open a new spreadsheet, and select the OpenAI integration on the welcome side panel. Alternatively, you can search for the integration inside the Data Actions panel.
Step 2: Search for "Extract" in the actions panel and select the action.
Finally, Connect your OpenAI account to get started.
The Free plan includes 50 free executions of the OpenAI integration.
Users on the Plus or Pro plans have unlimited access to OpenAI and can use their API key to access any OpenAI model, including fine-tuned models. By default, the OpenAI integrations use the "GPT-4o" model.
Step 3: Start extracting by typing the syntax in a blank space.
I'm extracting the phone number on the email copy for this example below. I typed in: =EXTRACT_OPENAI(“phone number”, A2)
Once done, the phone number in the email is automatically generated.
You can also extract key insights from a survey. For example, you've just run a survey to gather customer feedback for your SaaS beta version.
Pro tip 💡
Rows lets you pull and aggregate live data from 50+ sources, thanks to built-in data integrations in various domains:
- Marketing: GA4, GSC, Facebook, Instagram, TikTok, ...
- Productivity software: OpenAI, Notion, Slack, Email, Translate, ...
- Data Warehouse: MySQL, BigQuery, PostgreSQL, Snowflake, Amazon Redshift Additionally, if the source you want to pull data from is not in our catalog, you can create custom connections via our HTTP functions: GET, POST, PUT, PATCH, which makes the catalog of integrable tools virtually infinite.
Back to the steps, let's go through another example.
Once done with ingesting data to your Rows spreadsheet, type this syntax on the blank space: =EXTRACT_OPENAI("key insights", A2), assuming that A2 contains the first survey response.
Once done, our AI integration gives you insight into the response in seconds, saving you a ton of time. All you need to do is refine if need be.
Want to learn more about OPENAI integration on Rows and its use cases? Click here!
The spreadsheet where data comes to life
Connected to your business data. Powered by AI. Delightful to share.
Try Rows for FreeWhy Choose Rows for extracting substrings? AI-powered extraction for complex strings
Rows.com offers a powerful blend of multi-purpose extraction with AI that streamlines the traditionally complex and time-consuming task of inputting extended syntax.
With Rows.com, you can extract anything from any data structure.
What's more? You can easily manipulate your data using our built-in AI analyst. And, of course, we offer you a variety of dashboard templates to meet your everyday business tasks.
Ready to get started with Rows.com? Sign up here for free.
