Vídeo: Cómo extraer datos de las celdas de Google Sheets

Video transcript: Extract Data add-on for Google Sheets

Whenever you need to extract certain data from Google Sheets cells, our Extract add-on will work wonders:

• it extracts data followed or preceded by a certain string
• gets the required number of characters from any position
• pulls numbers, links, email addresses
• and lets you set up your own patterns to look for and extract from cells

Let’s see how this tool works directly in spreadsheets.

Start the add-on

Start Power Tools and go to Text > Extract.

Extract by strings

The first way you’ll see will get you everything that follows, precedes, or stands between particular text strings. Just select the range, tick off the required options and specify the strings or characters.

You can make the tool consider the text case of these strings by choosing this box as well.

Additional settings for each extraction way

Below are some additional settings that appear in each extraction group.

For example, if you think there are several occurrences of the desired string in a cell, you can not only get them all, but also place them into the same or separate cells.

There are also options to:

• extract data into a brand new column without overriding the neighbouring one
• and to cut the extracted pieces of data from their original cells

Get the result

Once you’re all set, click Extract, and get the data of interest in a new column.

There are 5 more ways to extract the data.

Extract the first/last N characters

For instance, the second group gets the exact number of characters from the beginning or the end of cells. All you have left to do is edit a name of a new column.

Extract by position

If your cells contain records of the same length, you will get the same result using the extraction by position. Just set the number of a character to start with and the total to extract. Press Extract and you’ll get the same set of the extracted data.

Extract numbers

The add-on also knows how to extract all numbers along with their decimals no matter the separator (which, by the way, you can specify). I don’t have these, so integers will do. But, as you can see, numbers here are separated by hyphens so I need all occurrences in one cell.

Perfect!

Extract hyperlinks, URLs, email addresses

The tool also gets entire hyperlinks, URLs only, or email addresses.

There, all email addresses have been extracted to a new column.

Extract by mask

Finally, you can use the last group of settings if there’s a particular pattern you’d like to find and extract. Enter your mask into a corresponding field, tweak additional options and press Extract.

You’ll see all records that match your mask in a new column.

You will find this add-on in the Google Sheets store along with 30 other time-savers for your spreadsheets.

If you have any questions, please contact us, we’ll be happy to help!

You may also be interested in

• Extract data from Google Sheets cells: text, numbers, URLs, hyperlinks, email addresses, dates, and time
• How to split cells in Google Sheets to columns and convert columns to rows
• Remove the same text or certain characters from multiple Google Sheets cells at once
• Find and replace special characters in Google Sheets

{
"@context": "https://schema.org”,
"@type": "Article",
"url": "https://www.ablebits.com/office-addins-blog/google-sheets-extract-data-video/”,
"mainEntityOfPage": "https://www.ablebits.com/office-addins-blog/google-sheets-extract-data-video/”,
"inLanguage": "en-us",
"headline": "Video: How to extract data from Google Sheets cells",
"description": "If you haven’t tried our Extract Data add-on for Google Sheets yet, this video will make up your mind. See how easy it is to extract links, email addresses, numbers and text from different positions of multiple Google Sheets cells at once.",
"author": {
"@type": "Person",
"name": "Natalia Sharashova",
"url": "https://www.ablebits.com/office-addins-blog/author/natalia-sharashova/”
},
"publisher": {
"@type": "Organization",
"name": "Ablebits.com",
"logo": {
"@type": "ImageObject",
"url": "https://cdn.ablebits.com/_img/d-19/logo/ablebits-logo-desktop.svg”
}
},
"datePublished": "2021-11-23",
"dateModified": "2021-11-23"
}