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, chemical name of 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 the structure of the text string.
The benefit of extracting text is numerous. You can apply 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 difference 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 SPLIT formula is not effective in this example? See the following formula and output.
“Pantoprazole” in one cell, “40” in second cell, “mg” in third cell, and “Tabs” in fourth cell.
The result of the above split formula is not the one that we want. It just splits the texts. What we want is specific information from cell A9 to different cells.
We can learn here how to extract specific texts from a cell in to different cells in Google Sheets with different suitable formulas.
Please refer the screenshot above to know the cell references used in the formulas below.
Formula in Cell B9
You can use the above REGEXEXTRACT formula to extract the first word of a cell content in Google Sheets. With the help of this REGEXEXTRACT formula, we can easily extract the drug name in our above example.
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 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 find the final formula.
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 a 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 suffix to it.
Still I’m not satisfied. 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 in to different cells. Here you should know how to extract multiple numbers from a text in Google Sheets. So my final formula in cell C2 is;
Here the split formula split 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 reference. Now the range is virtually like two cells. So an ARRAYFORMULA is must here.
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
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.
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 combination 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 FIND, LEN and MID functions to extract texts in Google Sheets. Hope you’ve enjoyed the stay here.