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
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.
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
, andTOM 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.
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?
Hi, Jason,
Try this one.
=REGEXEXTRACT(B10,"\–([\d\.]+)\)")
Replace the
–
in the formula with the one that copied from your text itself.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, Jason,
This would help.
=REGEXEXTRACT(B10,"[0-9/]+")
I have a cell with data posted as follows
D
L
W
W
I want it to show D L W W
Is there a way to do this?
Hi, Chris Durell
Assume cell B6 contains the said characters. I guess the characters are separated by New Lines. Either clean them or replace them with space characters.
To clean the New Line characters, use the following CLEAN formula.
=clean(B6)
To replace New Line characters with space characters, use the SUBSTITUTE formula.
=SUBSTITUTE(B6,char(10)," ")
Related: How to Move New Lines in a Cell to Columns in Google Sheets.
This is super helpful! Another question though, what if you wanted to extract: “Pantoprazole 40 mg” from A10?
Thanks!
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]*"))
Hi, Jason,
You haven’t mentioned what you want to extract!