HomeGoogle DocsSpreadsheetExtract Different Texts From a Cell in Google Sheets

Extract Different Texts From a Cell in Google Sheets

Published on

If you know how to extract different texts from a cell in Google Sheets, you can avoid lots of cut and paste and thus save your time. There are several real-life examples I can give you in this case.

Here is one such example. Just imagine you have a list of generic names of medicines in one column. From that, if you want to extract the product ID, the chemical name of a drug, dosage strength, etc. to new cells, you can use different Google Sheets functions for this.

Yup! Not one common function. The usage of functions to extract different texts from a Cell in Google Sheets depends on the structure of the text string.

The benefit of extracting text is numerous. You can apply a different filter to the extracted text and also can apply different conditional formatting rules to it.

How to Extract Different Texts From a Cell in Google Sheets

extract texts from left, middle, specific text, number in google sheets

Look at the image. In Column A, I have generic names of few medicines. In column B to E, I have extracted the required details form Column A.

From the Column A texts, you can split the name of the drug, dosage, etc. to separate cells.

Actually, if we just want to split the text into different cells, it’s easy. You can use the SPLIT function with space as delimiter here. But we want to know how to extract only required texts from a cell in Google Sheets. That is what I’ve done in Column B to E in the above example.

Why is SPLIT formula not effective in this example? See the following formula and output.

Formula:

=split(A9," ")

Result:

“Pantoprazole” in one cell, “40” in the second cell, “mg” in the third cell, and “Tabs” in the fourth cell.

The result of the above split formula is not the one that we want. It just splits the whole texts/words. What we want is specific information from cell A9 to different cells.

We can learn here how to extract specific texts from a cell into different cells in Google Sheets with different suitable formulas.

Please refer the screenshot above to know the cell references used in the formulas below.

The Formula in Cell B9

=REGEXEXTRACT(A9,"\w+")

You can use the above REGEXEXTRACT formula to extract the very first word from a text in a cell in Google Sheets.

With the help of this REGEXEXTRACT formula, we can easily extract the drug name in our above example.

Similar: Learn the use of REGEXMATCH in Google Sheets with examples.

The Formula in Cell C9

Now we want to extract the dosage of the medicine. You can do this if you know how to extract numbers from a text. Here again, you can use REGEXEXTRACT.

This following formula would extract the first number from a text string (here from cell A9) in Google Sheets. It’s not our final formula in C9. Just read on to get the final formula.

=REGEXEXTRACT(A9,"(\d+)")

Some drug may contain the dosage in both mg and ml. We want only the dosage in mg. So you should know how to extract a number from a text, based on the suffix to it in Google Sheets.

You can use the below formula for this. This formula would only extract a number from a text if the word “mg” is the suffix to it.

=REGEXEXTRACT(A9,"(\d+) mg")

Still, I’m not satisfied. Do you know why? Some generic names of medicines are like this.

“Domperidone 30 mg + Pantoprazole 40 mg Caps”

See the dosage in two places. I want both the dosage into different cells. Here you should know how to extract multiple numbers from a text in Google Sheets. So my final formula in cell C9 will be;

=ArrayFormula((REGEXEXTRACT(split(A9,"+"),"(\d+) mg")))

Formula Explanation:

Here the split formula splits the medicine names using “+” as the delimiter. Then I’ve applied the above last REGEXEXTRACT formula. But instead of cell reference A9, I’ve used the split formula as the reference.

Now the range is virtually like two cells. So an ARRAYFORMULA is must here.

The Formula in Cell D9

There is no formula in Cell D9 as the C9 formula itself expands the results to D9.

Related and Interesting:  Extract All Numbers from Text and SUM It in Google Sheets

The Formula in Cell E9

Here we just want to extract whether the medicine is “tabs”, “caps” or “inj”. This you can extract if you know how to extract specific characters from the last part of a string. You can straightaway use the RIGHT function here.

=right(A9,4)

This way you can extract different texts from a Cell in Google Sheets.

No matter the text that you want to extract is in the middle of a text, left, right or at any position. There are different combinations of text formulas for this.

One more thing. Your text string may be in a different structure and you may not find the above tips working for you. In certain cases, other than the REGEXEXTRACT and SPLIT, you can use combinations of FIND, LEN, and MID functions to extract texts in Google Sheets.

Hope you’ve enjoyed the stay here.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

12 COMMENTS

  1. Dear Prasanth,

    I would like to extract all words with capital letters from what is in cell A1. Let us say that the text in cell A1 is the below:

    MR SMITH: Hello my name is MS. JONES and the great TOM LOVE"

    Can one extract MR SMITH, MS. JONES, and TOM LOVE into separate columns?

    • Hi, Jason,

      I have this formula to extract uppercase strings. But it partially does the job.

      =split(regexreplace(A1,"[a-z:]+",)," ",0)

      Please note, in the formula, there are two spaces within double quotation marks. Please modify like that when you copy-paste the above formula into your sheet.

  2. Hi Prasanth.

    I was able to extract the first year using your REGEXEXTRACT(B10,"[0-9/]+") with this text in B10: “name, President of city (1996–2006)”

    Result: 1996

    But now I would like to extract the second year. Do you know how I can do that?

  3. Another related question

    How to extract the date from the text (when the date could have an unknown number of digits. This grabs the first digit.

    =REGEXEXTRACT(B10,"\d+")

    6/30/97: “Press Remarks with contry_name Foreign Minister name_of_minister, City, Country”

    • Hi, Amanda,

      You can use any of the below Regex.

      This Regex formula extracts phrase after the “+” sign.

      =trim(REGEXEXTRACT(A10, "\+(.+)"))

      This Regex is to extract the first three whole words after the “+” sign.

      =trim(regexextract(REGEXEXTRACT(A10, "\+(.+)"),"[\w]* [\w]* [\w]* [\w]*"))

      • VERY helpful. I have the following text in A10:
        “Press Remarks with contry_name Foreign Minister name_of_minister, City, Country”

        Note: Sample text modified by admin.

        But I get an error when I use the above command
        =trim(regexextract(REGEXEXTRACT(A10, "\+(.+)"),"[\w]* [\w]* [\w]* [\w]*"))

LEAVE A REPLY

Please enter your comment!
Please enter your name here