Reading Part of a Cell Google Sheets

This next bit of our operations with text in spreadsheets is devoted to extraction. Detect out means to extract various data — text, characters, numbers, URLs, email addresses, engagement & time, etc. — from various positions in multiple Google Sheets cells at once.

• Google Sheets formulas to extract text and numbers from strings
  • Excerpt data past position: showtime/last/centre N chars
  • Extract text/numbers
• Formula-free ways to extract data from Google Sheets cells
  • Extract different types of data using Power Tools addition
  • Extract date and time from timestamps

Google Sheets formulas to extract text and numbers from strings

Formulas in Google Sheets are everything. While some combos add together text & numbers and remove various characters, some of them too extract text, numbers, divide characters, etc.

Excerpt data by position: outset/last/middle Due north chars

The easiest functions to deal with when you're nearly to accept out information from Google Sheets cells are LEFT, Correct, and MID. They get whatsoever data by position.

Excerpt data from the beginning of cells in Google Sheets

Y'all can easily pull out the first N characters using the LEFT function:

LEFT(cord,[number_of_characters])

• string is the text where you want to extract information from.
• number_of_characters is the number of characters to take out starting from the left.

Here's the simplest example: let'due south have out the country codes from the phone numbers:

Equally you tin can see, country codes take 6 symbols at the get-go of cells, then the formula you need is:

=LEFT(A2,six)

Tip. ArrayFormula volition go far possible to get 6 characters from the entire range at once:

=ArrayFormula(LEFT(A2:A7,6))

Excerpt data from the terminate of cells in Google Sheets

To pull out the concluding Due north characters from cells, use the Right function instead:

Right(string,[number_of_characters])

• string is nevertheless the text (or a cell reference) to excerpt data from.
• number_of_characters is too the number of characters to take from the right.

Let's get that country names from the aforementioned phone numbers:

They accept but 2 characters and that's exactly what I mention in the formula:

=Correct(A2,2)

Tip. ArrayFormula volition besides help y'all extract information from the end of all Google Sheets cells at once:

=ArrayFormula(RIGHT(A2:A7,2))

Extract data from the middle of cells in Google Sheets

If there are functions to excerpt information from the commencement and the end of cells, in that location must be a function to extract data from the center likewise. And yes — there is one.

Information technology's called MID:

MID(string, starting_at, extract_length)

• string — the text where you want to accept out the eye part from.
• starting_at — the position of the graphic symbol from which yous want to first getting the information.
• extract_length — the number of characters y'all need to pull out.

By the example of the aforementioned telephone numbers, let's discover the phone numbers themselves without their country codes and country abbreviation:

As the country codes end with the sixth character and the 7th is the dash, I will pull numbers starting from the eighth digit. And I'll go eight digits in total:

=MID(A2,eight,eight)

Tip. Irresolute one cell to the unabridged range and wrapping it in ArrayFormula will provide you lot with the result for each jail cell at in one case:

=ArrayFormula(MID(A2:A7,8,8))

Extract text/numbers from strings

Sometimes extracting text past position (as shown above) is not an option. The required strings may reside in whatever function of your cells and consist of a different number of characters forcing y'all to create different formulas for each prison cell.

But Google Sheets wouldn't be Google Sheets if it didn't have other functions that would help to extract text from strings.

Permit's review a few possible means spreadsheets offer.

Extract information before a certain text — LEFT+SEARCH

Whenever y'all want to extract data that precedes a certain text, use LEFT + SEARCH:

• LEFT is used to render a certain number of characters from the beginning of cells (from their left)
• SEARCH looks for certain characters/strings and gets their position.

Combine these — and LEFT will return the number of characters suggested by SEARCH.

Hither's an instance: how practice you extract textual codes before each 'ea'?

This is the formula that will help you lot in similar cases:

=LEFT(A2,SEARCH("ea",A2)-1)

Here'south what happens in the formula:

1. SEARCH("ea",A2) looks for 'ea' in A2 and returns the position where that 'ea' starts for each cell — x.
2. So 10th position is where 'e' resides. But since I want everything correct earlier 'ea', I demand to subtract 1 from that position. Otherwise, 'eastward' will be returned besides. So I get nine eventually.
3. LEFT looks at A2 and gets the starting time nine characters.

Extract data afterwards the text

In that location are besides means to become everything after a certain text string. But this fourth dimension, Right won't help. Instead, REGEXREPLACE takes its turn.

Tip. REGEXREPLACE uses regular expressions. If you lot're not ready to deal with them, there's a much easier solution described below.

REGEXREPLACE(text, regular_expression, replacement)

• text is a string or a prison cell where you lot desire to make changes
• regular_expression is the combination of characters that stands for a part of the text that you're looking for
• replacement is whatever you want to go instead of that text

So, how practise you utilize information technology to extract data after a certain text — 'ea' in my example?

Like shooting fish in a barrel — using this formula:

=REGEXREPLACE(A2,"(.*)ea(.*)","$two")

Allow me explain how this formula works exactly:

1. A2 is a cell I'yard extracting the data from.
2. "(.*)ea(.*)" is my regular expression (or you tin can call it a mask). I look for 'ea' and put all other characters into brackets. At that place are 2 groups of characters — everything before 'ea' is the showtime grouping (.*) and everything afterwards 'ea' is the second one (.*). The unabridged mask itself is put to double-quotes.
3. "$2" is what I want to become — the second grouping (hence its number ii) from the previous statement.

Tip. All characters used in regular expressions are collected on this special page.

Excerpt numbers from Google Sheets cells

What if you lot desire to extract simply numbers when their position and whatever goes before & afterward doesn't matter?

Masks (a.chiliad.a. regular expressions) volition also help. In fact, I'll take the same REGEXREPLACE function and change the regular expression:

=REGEXREPLACE(A2,"[^[:digit:]]", "")

1. A2 is a prison cell where I want to get those numbers from.
2. "[^[:digit:]]" is a regular expression that takes everything merely digits. That ^caret symbol is what makes an exception for digits.
3. "" replaces everything except numeric characters with "aught". Or, in other words, removes information technology entirely, leaving only numbers in cells. Or, extracts numbers :)

Extract text ignoring numbers and other characters

In a similar fashion, y'all tin can take out only alphabetic data from Google Sheets cells. The contraction for the regular expression that stands for text is called accordingly — blastoff:

=REGEXREPLACE(A2,"[^[:alpha:]]", "")

This formula takes everything just letters (A-Z, a-z) and literally replaces information technology with "nothing". Or, to put it in another style, takes out only letters.

Formula-free ways to extract information from Google Sheets cells

If you're looking for an easy formula-free mode to extract various types of data, you've come to the right place. Our Ability Tools add-on has just the tools for the job.

Extract different types of data using Ability Tools add-ons

The beginning tool I'd like you to know is called Extract. It does exactly what you've come looking for in this article — extracts different types of data from Google Sheets cells.

User-friendly settings

All the cases I've covered above are non just solvable with the add-on. The tool is convenient and so all you demand to practise is select the range you want to process and tick off the required checkboxes. No formulas, no regular expressions.

Call back the second indicate of this article with REGEXREPLACE and regular expressions? Hither's how unproblematic it is for the add-on:

Extra-options

As y'all can see, there are some extra options (but checkboxes) that you can speedily turn on/off to get the most precise event:

1. Become the strings of the required text case merely.
2. Pull out all occurrences from each jail cell and identify them in one jail cell or separate columns.
3. Insert a new column with the result to the right of the source information.
4. Clear the extracted text from the source information.

Excerpt different data types

Not simply Power Tools extracts data before/after/between certain text strings and the kickoff/last N characters; but it also takes out the following:

1. Numbers forth with their decimals keeping the decimal/thousands separators intact:
   
2. Northward characters starting from a certain position in a jail cell.
3. Hyperlinks (text + link), URLs (link), email addresses.

Extract whatever string of data from everywhere

There'due south too an choice to fix your own verbal pattern and use information technology for the extraction. Extract by mask and its wildcard characters — * and ? — do the play tricks:

• For example, yous can bring out everything between the brackets using the following mask: (*)
• Or get those SKUs that have only v numbers in their ids: SKU?????
• Or, as I show on the screenshot below, pull everything after each 'ea' in each cell: ea*

Excerpt date and time from timestamps

As a bonus, there's a smaller tool that will excerpt date and fourth dimension from timestamps — it's called Split Date & Time.

Although it was created to split timestamps in the first place, it'south perfectly capable of getting 1 of the desired units individually:

Just select one of the checkboxes depending on what you want to extract — date or time — from timestamps in Google Sheets and hitting Split. The required unit will be copied over to a new column (or it will supercede the original data if you select the final checkbox every bit well):

This tool is also part of the Power Tools add together-on so once you install it to get whatsoever data from Google Sheets cells, information technology's got you covered completely. If not, please get out a comment and we'll aid you out :)

You may also exist interested in

crouseblocared.blogspot.com

Source: https://www.ablebits.com/office-addins-blog/2021/07/08/extract-data-google-sheets/

Share this post