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

Si aún no has probado nuestro complemento de extracción de datos para Google Sheets, este vídeo te hará decidirte. Comprueba lo fácil que es extraer enlaces, direcciones de correo electrónico, números y texto de diferentes posiciones de varias celdas de Google Sheets.

“@context”: “http://schema.org”,
“@type”: “VideoObject”,
“name”: “How to extract data from Google Sheets cells”,
“description”: “Extract text, numbers, URLs and other data from Google Sheets cells: by strings, by position, or using your own masks.”,
“thumbnailUrl”: “https://i.ytimg.com/vi/4Gq_dJKb6iE/default.jpg”,
“uploadDate”: “2021-11-16T13:42:11Z”,
“duration”: “PT3M10S”,
“embedUrl”: “https://www.youtube.com/embed/4Gq_dJKb6iE”,
“interactionCount”: “62”

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

How the add-on works.

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

See all additional options directly in Power Tools.

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.
Pull all data starting from the 5th character.

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.
Get only numbers from each cell.

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.
See email addresses in separate cells.

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.
Extract data by your own custom pattern.

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

“@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”

Google Sheets: artículos destacados

  • Cómo combinar datos de filas duplicadas basándose en una columna única
  • Cómo comparar datos en dos hojas o columnas de Google Sheets
  • VLOOKUP de Google Sheets con ejemplos

var b20CategorySlug = “google-sheets-extract-data-video”;

Tabla de contenidos

Scroll to Top