Although there is no such thing as Substring function in Excel, there exist three Text functions to extract text of a given length from a cell. Additionally, there are FIND and SEARCH functions to get a substring before or after a specific character. Continue reading
Comments on: Excel substring: how to extract text from cell
by Svetlana Cheusheva, updated on
Comments page 4. Total comments: 306
"Prescriber: Mitchell CNM H Becky
Patient: Patient Name
Medication: FE TABS 325 (65 FE) MG ORAL TABLET DELAYED RELEASE; Qty: 60 Refills: PRN
Ordered: 29-Dec-2017@1130
Signed/Transmitted: 29-Dec-17@1130
Pharmacy: Access Family Care Pharmacy*
530 South Maiden Lane
Joplin, MO
Ph: 4177827209
Fax: 4177827727"
Above is a text field in an excel spreadsheet. I need to make the medication in a field by itself.
Can you tell me how to pull what is after Medication:?
I'm sure there may be a more efficient way but you can do the following:
=MID(A1, SEARCH("Medication: ", A1) + 12,SEARCH(";", A1, SEARCH("Medication: ", A1)) - (SEARCH("Medication: ", A1)+12))
Assuming your text is in A1.
FE TABS 325 (65 FE) MG ORAL TABLET DELAYED RELEASE; Qty: 60 Refills: PRN
I will assume all the strings are in the format "Medication; Qty"
1) To extract the medication, or what's before the semicolon, use:
=LEFT(String, SEARCH(";",String)-1)
2) To extract the Qty (and what's after), use:
=RIGHT(String,LEN(String)-SEARCH(";",String))
Nice primer, however, extracting a number from a string leaves me with another string. When I convert these strings to numbers via format I can't operate on them as numbers, they are still strings?
Hi Steven,
Right, whenever you use a Text function (Right, Left, Mid) to extract something, the output is always text. In case of extracting a number from a string, the result is a numeric substring, which in terms of Excel is also text, not number. To convert it to number, you can wrap your formula in the VALUE function. Here's an example in the simplest form:
=VALUE(LEFT(A2,4))
And thank you for a smart question! I've added this tip to the article.
Or else you can multiply it by 1, with iferror.