Extract text, numbers, links from Google Sheets cells

Start the add-on

1. Run Power Tools from the Google Sheets menu: Extensions > Power Tools > Start:
   
2. Go to the Text group:
   
3. Find and click the Extract icon:
   

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

Just 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.

Extract text & other data in Google Sheets by strings

This first group pulls everything after or before the desired substring:

You can tick off both checkboxes — all after text & all before text — to get everything between two specified substrings.

There are 4 additional options that make the process 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 text and other 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 occurrences in separate cells (columns to the right of the source cells) or one cell (separated by a space).

• By default, the add-on pulls values to the neighboring column on the right of the selected cells. Even if there are some records already, they 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 to cut all pieces from their original column and paste them into the right column.

  Or keep it unchecked if you'd rather simply copy the required pieces.

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

How to pull out the first/last N characters

The second group will find and take out the exact number of symbols from the beginning or from the end of each cell:

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 characters will be taken and put to the existing column to the right overwriting any records lying there.
• Select the second option to cut the extracted text and other characters from their original column (rather than just copy them) and paste 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:

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 bring out 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, your 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.

Extract data in Google Sheets by position

This one lets you define not only the number of chars to take out but also the exact starting 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 and '6' and the total number of chars to get.

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 new records.

  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 so the records are cut from their original cells and pasted to the cells on their right.

  Or keep it unchecked if you'd rather simply copy those pieces.

Once you're ready, press the final Extract button to extract text and other data by position.

How to extract hyperlinks, URLs, email addresses

Use the Extract links groups to obtain the following:

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

Those additional settings are here as well:

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

  Here you can also decide whether to place all instances 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 add-on will extract hyperlinks/ email addresses/ URLs and will put them to the existing column to the right overwriting any records lying there.
• Select the third option to cut the 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 the final button to extract those hyperlinks to the next column.

Get values by mask (repeated pattern)

Extract by mask lets you set up the exact pattern (mask) to look for in the selected cells and pull out all matching records:

The following wildcard characters will help you determine the exact position:

• * — stands for any string. For example, the pattern New* will pull out New York, Newcastle.
• ? — stands for one char. For example, the mask ???42 will get 17142, SKU42.
• Feel free to combine both, e.g. ??? * will take out 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 obtain all matching instances. The Extract all occurrences checkbox will help you out.

  Here you can also decide whether to place all occurrences 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 pieces from their original cells (rather than just copy them if the option is unchecked) and paste them to the right column.

Once you're ready, press the finishing button to extract the required text and other data to the neighbouring column.