This utility from Power Tools lets you extract text in Google Sheets, 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.
By default, the add-on pulls all strings into the existing column to the right of the selected data. Even if the column is not empty, the results overwrite your records. No worries! You can still place them in a new column by selecting the corresponding option in the tool.
The add-on also processes cells in filtered & hidden rows & columns.
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.
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:
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).
To avoid that, tick this box to insert a new column with the results to the right of the source cell.
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.
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:
Hit Extract to pull the first/last N characters to the right.
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:
The same extra options can be used here:
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).
Click Extract to take out numbers from the selected cells.
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:
To avoid that, tick this box to insert a new column with the results to the right of the source cell.
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.
Use the Extract links groups to obtain the following:
Those additional settings are here as well:
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).
When you're ready, hit the final button to extract those hyperlinks to the next column.
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:
4 extra options will make your search by mask even more precise:
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).
Once you're ready, press the finishing button to extract the required text and other data to the neighbouring column.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
