How to remove dosage info from the drug name in excel?

**nightxiao** · 03-20-2014, 05:57 PM

Hi guys,

My original data is like below -

ABACAVIR 20 MG/ML
ABACAVIR 300 MG
ABACAVIR 300 MG / LAMIVUDINE 150 MG / ZIDOVUDINE 300 MG
ABACAVIR 600 MG / LAMIVUDINE 300 MG

I want to remove all the dosage info from the drug names, so they will become -

ABACAVIR
ABACAVIR
ABACAVIR / LAMIVUDINE / ZIDOVUDINE
ABACAVIR / LAMIVUDINE

Can any of you help me on this? Thanks a lot!

**AZ-XL** · 03-20-2014, 06:13 PM

Your list starts from a1 cell.
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0",""),"MG",""),"ML",""))

This is longest solution actually. I am sure here excel experts will suggest smarter formulas.

**Draconi** · 03-20-2014, 06:21 PM

Here is a spreadsheet that allows you to remove specific words.

**AlKey** · 03-20-2014, 07:32 PM

Here it is

in B1

Please Login or Register  to view this content.

	A	B
1	ABACAVIR 20 MG/ML	ABACAVIR /
2	ABACAVIR 300 MG	ABACAVIR
3	ABACAVIR 300 MG / LAMIVUDINE 150 MG / ZIDOVUDINE 300 MG	ABACAVIR / LAMIVUDINE / ZIDOVUDINE
4	ABACAVIR 600 MG / LAMIVUDINE 300 MG	ABACAVIR / LAMIVUDINE