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 multiple ways to extract your data.

How to extract data from the selected cells in Google Sheets

Select a column (or a range of cells within a column) where you want to pull out data from and choose one of the options based on your task:
Seven settings to extract different types of data.

  1. Extract the first/last characters will find and pull the exact number of symbols from the beginning or from the end of each cell.
  2. Extract by strings pulls everything after or before the entered substring.
    Tip. Tick off both checkboxes (after & before) to extract everything between two specified substrings.
    Tip. If the text case matters, select the Match case option to consider text written in different cases.
  3. Extract by position lets you define not only the number of chars to extract but also the exact starting position for extraction.

    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' as the total number of chars to get.

  4. Extract numbers will pull all numeric characters from the selected cells.
  5. This one extracts one of the following:
  6. By default, the add-on puts the extracted data to a column to the right of the selected cells. Even if the column already has data, it will be overwritten by the extracted text.

    To have an additional empty column and paste the result there instead, use this option — Place results to a new column.

  7. 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 Extract and get the required data in the neighbouring column.

Related pages

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.