Extract data from Google Sheets cells: text, numbers, URLs, hyperlinks, email addresses, dates, and time

This next bit of our operations with text in spreadsheets is devoted to extraction. Find out ways to extract various data — text, characters, numbers, URLs, email addresses, date & time, etc. — from various positions in multiple Google Sheets cells at once.

Google Sheets formulas to extract text and numbers from strings

Formulas in Google Sheets are everything. While some combos add text & numbers and remove various characters, some of them also extract text, numbers, separate characters, etc.

Extract data by position: first/last/middle N chars

The easiest functions to deal with when you're about to take out data from Google Sheets cells are LEFT, RIGHT, and MID. They get any data by position.

Extract data from the beginning of cells in Google Sheets

You can easily pull out the first N characters using the LEFT function:

LEFT(string,[number_of_characters])
  • string is the text where you want to extract data from.
  • number_of_characters is the number of characters to take out starting from the left.

Here's the simplest example: let's take out the country codes from the phone numbers:
Phone numbers with country codes.
As you can see, country codes take 6 symbols at the beginning of cells, so the formula you need is:

=LEFT(A2,6)
Get 6 first characters from each cell.

Tip. ArrayFormula will make it possible to get 6 characters from the entire range at once:

=ArrayFormula(LEFT(A2:A7,6))
Use ArrayFormula to extract data from the beginning of all Google Sheets cells at once.

Extract data from the end of cells in Google Sheets

To pull out the last N characters from cells, use the RIGHT function instead:

RIGHT(string,[number_of_characters])
  • string is still the text (or a cell reference) to extract data from.
  • number_of_characters is also the number of characters to take from the right.

Let's get that country names from the same phone numbers:
Phone numbers with country codes.
They take only 2 characters and that's exactly what I mention in the formula:

=RIGHT(A2,2)
Copy 2 last characters from each cell.

Tip. ArrayFormula will also help you extract data from the end of all Google Sheets cells at once:

=ArrayFormula(RIGHT(A2:A7,2))
Extract data from the end of all Google Sheets cells at once using the ArrayFormula.

Extract data from the middle of cells in Google Sheets

If there are functions to extract data from the beginning and the end of cells, there must be a function to extract data from the middle as well. And yes — there is one.

It's called MID:

MID(string, starting_at, extract_length)
  • string — the text where you want to take out the middle part from.
  • starting_at — the position of the character from which you want to start getting the data.
  • extract_length — the number of characters you need to pull out.

By the example of the same phone numbers, let's find the phone numbers themselves without their country codes and country abbreviation:
Phone numbers with country codes.
As the country codes end with the 6th character and the 7th is the dash, I will pull numbers starting from the 8th digit. And I'll get 8 digits in total:

=MID(A2,8,8)
Bring out 8 characters from the middle of the string.

Tip. Changing one cell to the entire range and wrapping it in ArrayFormula will provide you with the result for each cell at once:

=ArrayFormula(MID(A2:A7,8,8))
Use ArrayFormula to extract data from the middle of all Google Sheets cells at once.

Extract text/numbers from strings

Sometimes extracting text by position (as shown above) is not an option. The required strings may reside in any part of your cells and consist of a different number of characters forcing you to create different formulas for each cell.

But Google Sheets wouldn't be Google Sheets if it didn't have other functions that would help to extract text from strings.

Let's review a few possible ways spreadsheets offer.

Extract data before a certain text — LEFT+SEARCH

Whenever you want to extract data that precedes a certain text, use LEFT + SEARCH:

  • LEFT is used to return a certain number of characters from the beginning of cells (from their left)
  • SEARCH looks for certain characters/strings and gets their position.

Combine these — and LEFT will return the number of characters suggested by SEARCH.

Here's an example: how do you extract textual codes before each 'ea'?
Bring out all data before 'ea'.
This is the formula that will help you in similar cases:

=LEFT(A2,SEARCH("ea",A2)-1)
Extract all data before a ceratin text in Google Sheets.
Here's what happens in the formula:

  1. SEARCH("ea",A2) looks for 'ea' in A2 and returns the position where that 'ea' starts for each cell — 10.
  2. So 10th position is where 'e' resides. But since I want everything right before 'ea', I need to subtract 1 from that position. Otherwise, 'e' will be returned as well. So I get 9 eventually.
  3. LEFT looks at A2 and gets the first 9 characters.

Extract data after the text

There are also means to get everything after a certain text string. But this time, RIGHT won't help. Instead, REGEXREPLACE takes its turn.

Tip. REGEXREPLACE uses regular expressions. If you're not ready to deal with them, there's a much easier solution described below.
REGEXREPLACE(text, regular_expression, replacement)
  • text is a string or a cell where you want to make changes
  • regular_expression is the combination of characters that stands for a part of the text that you're looking for
  • replacement is whatever you want to get instead of that text

So, how do you use it to extract data after a certain text — 'ea' in my example?
Extract all data after 'ea'.
Easy — using this formula:

=REGEXREPLACE(A2,"(.*)ea(.*)","$2")
Use REGEXREPLACE to extract all data after a certain text.
Let me explain how this formula works exactly:

  1. A2 is a cell I'm extracting the data from.
  2. "(.*)ea(.*)" is my regular expression (or you can call it a mask). I look for 'ea' and put all other characters into brackets. There are 2 groups of characters — everything before 'ea' is the first group (.*) and everything after 'ea' is the second one (.*). The entire mask itself is put to double-quotes.
  3. "$2" is what I want to get — the second group (hence its number 2) from the previous argument.
Tip. All characters used in regular expressions are collected on this special page.

Extract numbers from Google Sheets cells

What if you want to extract only numbers when their position and whatever goes before & after doesn't matter?

Masks (a.k.a. regular expressions) will also help. In fact, I'll take the same REGEXREPLACE function and change the regular expression:

=REGEXREPLACE(A2,"[^[:digit:]]", "")
Regular expression to extract numbers from Google Sheets cells.

  1. A2 is a cell where I want to get those numbers from.
  2. "[^[:digit:]]" is a regular expression that takes everything but digits. That ^caret symbol is what makes an exception for digits.
  3. "" replaces everything except numeric characters with "nothing". Or, in other words, removes it entirely, leaving only numbers in cells. Or, extracts numbers :)

Extract text ignoring numbers and other characters

In a similar fashion, you can take out only alphabetic data from Google Sheets cells. The contraction for the regular expression that stands for text is called accordingly — alpha:

=REGEXREPLACE(A2,"[^[:alpha:]]", "")
Pull out alphabetic data from Google Sheets cells.

This formula takes everything but letters (A-Z, a-z) and literally replaces it with "nothing". Or, to put it in another way, takes out only letters.

Formula-free ways to extract data from Google Sheets cells

If you're looking for an easy formula-free way to extract various types of data, you've come to the right place. Our Power Tools add-on has just the tools for the job.

Extract different types of data using Power Tools add-ons

The first tool I'd like you to know is called Extract. It does exactly what you've come looking for in this article — extracts different types of data from Google Sheets cells.

User-friendly settings

All the cases I've covered above are not just solvable with the add-on. The tool is user-friendly so all you need to do is select the range you want to process and tick off the required checkboxes. No formulas, no regular expressions.

Remember the second point of this article with REGEXREPLACE and regular expressions? Here's how simple it is for the add-on:
Take out all after 'ea' using Power Tools.

Extra-options

As you can see, there are some extra options (just checkboxes) that you can quickly turn on/off to get the most precise result:

  1. Get the strings of the required text case only.
  2. Pull out all occurrences from each cell and place them in one cell or separate columns.
  3. Insert a new column with the result to the right of the source data.
  4. Clear the extracted text from the source data.

Extract different data types

Not only Power Tools extracts data before/after/between certain text strings and the first/last N characters; but it also takes out the following:

  1. Numbers along with their decimals keeping the decimal/thousands separators intact:
    Use Power Tools to extract numbers with decimals.
  2. N characters starting from a certain position in a cell.
  3. Hyperlinks (text + link), URLs (link), email addresses.

Extract any string of data from everywhere

There's also an option to set up your own exact pattern and use it for the extraction. Extract by mask and its wildcard characters — * and ? — do the trick:

  • For example, you can bring out everything between the brackets using the following mask: (*)
  • Or get those SKUs that have only 5 numbers in their ids: SKU?????
  • Or, as I show on the screenshot below, pull everything after each 'ea' in each cell: ea*

Extract data in Google Sheets by mask.

Extract date and time from timestamps

As a bonus, there's a smaller tool that will extract date and time from timestamps — it's called Split Date & Time.

Although it was created to split timestamps in the first place, it's perfectly capable of getting one of the desired units individually:
Split Date & Time add-on.
Just select one of the checkboxes depending on what you want to extract — date or time — from timestamps in Google Sheets and hit Split. The required unit will be copied over to a new column (or it will replace the original data if you select the last checkbox as well):
Extract date from timestamps in Google Sheets.
This tool is also part of the Power Tools add-on so once you install it to get any data from Google Sheets cells, it's got you covered completely. If not, please leave a comment and we'll help you out :)

You may also be interested in

Table of contents

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)