Extract data from the selected cells in Google Sheets

The Extract tool from Power Tools lets you pull out text, numbers, hyperlinks or their URLs, and email addresses from any part of the selected cells. Having this add-on in your arsenal, you can put Google Sheets FIND function, LEFT, MID, RIGHT and their nested formulas aside.

Before you start

By default, all results fall into the existing column to the right of the selected data. Even if the column is not empty, the add-on will overwrite your records. No worries! You can still get the result in a new column by selecting the corresponding option in the tool.

How to use the Extract tool

Start the add-on

  1. Run Power Tools from the Google Sheets menu: Add-ons > Power Tools > Start:
    Open Power Tools.
  2. Go to the Text group:
    A group of tools to work with text.
  3. Find and click the Extract icon:
    The Extract add-on for Google Sheets.

The add-on will open on the sidebar with 6 ways to extract your data: extract by strings, extract the first/last N characters, extract numbers, extract by position, extract links & email addresses, extract by mask.

Select a column (or a range of cells within a column) where you want to pull out data from and expand one of the groups based on your task.

How to extract data in Google Sheets by strings

Extract by strings pulls everything after or before the entered substring:
Extract data in Google Sheets by strings.
You can tick off both checkboxes — all after text & all before text — to extract everything between two specified substrings.

There are 4 additional options that make the extraction even more flexible:

  • If the text case of the strings you enter matters, select the Match case option to consider text written in different cases.
  • If the string of interest occurs a few times within a cell, you can make the add-on extract data before/after each instance of your string. The Extract all occurrences checkbox will help you out.

    Here you can also decide whether to place all extracted parts to separate cells (columns to the right of the source cells) or one cell (separated by a space).

  • By default, the add-on puts the extracted data to a column to the right of the selected cells. Even if the column already contains data, it will be overwritten by the extracted text.

    To avoid that, tick this box to insert a new column with the results to the right of the source cell.

  • Select the last option and the extracted data will be cut from its original column and pasted to the right column.

    Or keep it unchecked if you'd rather simply copy the extracted data.

Press Extract and get data before/after a specified string pulled to a neighbouring column.

How to extract the first/last N characters

The second group — Extract the first/last characters — will find and pull the exact number of symbols from the beginning or from the end of each cell:
Extract the first/last N characters.
Two additional settings here as well:

  • Insert new column with result to the right does exactly what it says. If you don't check this box, the extracted characters will be put to the existing column to the right and will overwrite any records lying there.
  • Select the second option to cut the extracted characters from their original column (rather than just copy if the box is unchecked) and paste them to the right column.

Hit Extract to pull the first/last N characters to the right.

How to extract numbers from Google Sheets cells

Extract numbers will pull all numeric characters from the selected cells:
Extract numbers from Google Sheets cells.
In case your numbers contain any separators, you'll be able to tell that to the add-on using the first two checkboxes. Whether decimals or thousands, select the necessary box and pick your separator from the drop-down:

  • Decimals: period (.) and comma (,)
  • Thousands: comma (,), period (.), space ( ), underscore (_) and apostrophe (')

The same extra options can be used here:

  • If numbers occur a few times within a cell, you can make the add-on extract all instances. The Extract all occurrences checkbox will help you out.

    Here you can also decide whether to place all extracted numbers to separate cells (columns to the right of the source cells) or one cell (separated by a space).

  • Insert new column with result to the right does exactly what it says. If you don't check this box, the extracted numbers will be put to the existing column to the right and will overwrite any records lying there.
  • Select the third option to cut the extracted numbers from their original column (rather than just copy them if unchecked) and paste them to the right column.

Click Extract to take out numbers from the selected cells.

How to extract data in Google Sheets by position

Extract by position lets you define not only the number of chars to extract but also the exact starting position for extraction:
Extract data in Google Sheets by position.
For example, to get 427-AB from SKU-427-AB-000 you will need to set '5' as the position of the 1st character to extract and '6' and the total number of chars to get.

Tip. You are free to leave The number of chars to extract unchecked to extract all symbols starting from a certain position.

Make use of 2 extra settings in this group:

  • By default, the add-on puts the extracted data to a column to the right of the selected cells. Even if the column already contains data, it will be overwritten by the extracted text.

    To avoid that, tick this box to insert a new column with the results to the right of the source cell.

  • Select the last option and the extracted data will be cut from its original column and pasted to the right column.

    Or keep it unchecked if you'd rather simply copy the extracted data.

Once you're ready, press the final Extract button to extract the data.

Use the Extract links groups to extract the following:

  • hyperlinks (a text with its URL)
  • URLs (just a link used on the text)
  • email addresses

Extract hyperlinks, URLs, and email addresses in Google Sheets.
Those additional settings are here as well:

  • If the link / email address / URL occurs a few times within a cell, you can make the add-on extract all instances. The Extract all occurrences checkbox will help you out.

    Here you can also decide whether to place all extracted parts to separate cells (columns to the right of the source cells) or one cell (separated by a space).

  • Insert new column with result to the right does exactly what it says. If you don't check this box, the extracted links / email addresses / URLs will be put to the existing column to the right and will overwrite any records lying there.
  • Select the third option to cut the extracted data from its original column (rather than just copy it if the option is unchecked) and paste it to the right column.

When you're ready, hit Extract to copy/cut those links to the next column.

How to extract data by mask

Extract by mask lets you set up the exact pattern (mask) to look for in the selected cells and pull out all matching records:
Extract data by mask in Google Sheets.
The following wildcard characters will help you determine the exact position:

  • * — stands for any string. For example, New* will extract New York, Newcastle.
  • ? — stands for one char. For example, ???42 will extract 17142, SKU42.
  • Feel free to combine both, e.g. ??? * will extract New York, Los Angeles.

4 extra options will make your search by mask even more precise:

  • Make the search case-sensitive by selecting the Match case
  • If the mask of interest occurs a few times within a cell, you can make the add-on extract all matching instances. The Extract all occurrences checkbox will help you out.

    Here you can also decide whether to place all extracted parts to separate cells (columns to the right of the source cells) or one cell (separated by a space).

  • Insert new column with result to the right does exactly what it says. If you don't check this box, the extracted data will be put to the existing column to the right and will overwrite any records lying there.
  • Select the last option to cut the extracted data from its original column (rather than just copy it if the option is unchecked) and paste it to the right column.

Once you're ready, press Extract and get the required data in the neighbouring column.

Related pages