If you’re tired of manually extracting different texts from a single cell in Google Sheets, then this article is for you! We’ll show you how to save time and avoid the hassle of cut and paste with some nifty Google Sheets functions. From extracting product IDs to chemical names and dosage strengths, we’ve got you covered. So let’s dive in!
Understanding the Power of Text Extraction
Imagine you have a column filled with generic names of medicines. Now, what if you need to extract specific details like the product ID, chemical name, and dosage strength into separate cells? This is where knowing how to extract different texts from a cell in Google Sheets comes in handy. Each text extraction function caters to a specific structure of the text string, giving you the flexibility to extract the required information with ease.
How to Extract Different Texts From a Cell in Google Sheets
Take a look at the image below. In Column A, we have a list of generic medicine names, and in Columns B to E, we’ll extract the necessary details from Column A.
Now, if you simply want to split the text into separate cells, you can use the SPLIT function with a space as the delimiter. However, if you want to extract specific information from a cell, we’ll need to employ different formulas. Let’s explore how to do that!
Extracting the Drug Name
To extract the very first word from a text in a cell, you can use the REGEXEXTRACT formula. In cell B9, enter the following formula:
=REGEXEXTRACT(A9, "w+")This formula will give you the drug name extracted from the text in cell A9.
Extracting the Dosage
To extract the dosage of the medicine, you can use the REGEXEXTRACT formula again to extract numbers from a text. In cell C9, enter the following formula:
=REGEXEXTRACT(A9, "d+")The above formula will extract the first number found in the text string of cell A9. But what if you only want the dosage in milligrams (mg)? Here’s the modified formula:
=REGEXEXTRACT(A9, "d+ mg")This formula will only extract the number if it is followed by the suffix “mg.”
However, some medicine names can have multiple dosages, like “Domperidone 30 mg + Pantoprazole 40 mg Caps.” To extract both dosages into separate cells, use the following formula in cell C9:
=ArrayFormula((REGEXEXTRACT(split(A9, "+"), "d+ mg")))Here, the split formula separates the medicine names using the “+” delimiter, allowing us to extract multiple numbers.
Extracting the Medicine Form
In cell E9, we want to extract whether the medicine is in tablet (tabs), capsule (caps), or injection (inj) form. You can achieve this by using the RIGHT function:
=RIGHT(A9, 4)This formula will extract the last four characters from the text in cell A9, giving us the medicine form.
And there you have it! With these formulas, you can easily extract different texts from a cell in Google Sheets. Whether the text is in the middle, left, right, or any position, there’s a combination of text formulas to suit your needs.
It’s important to note that if your text string follows a different structure, you may need to employ a combination of other functions like FIND, LEN, and MID to extract the desired information. But don’t worry, Google Sheets offers a wide range of functions to cater to various scenarios.
We hope you’ve found this article helpful and that you’re ready to save time and effort in extracting different texts from your Google Sheets cells. For more expert tips and tricks, visit Crawlan.com, your go-to resource for all things Google Sheets.
Happy extracting, and enjoy your newfound efficiency!
