remove specific words in a cell

pdjm12

New Member
Joined
Jun 19, 2019
Messages
11
Hi My excel has a column like this one:

Telmisartan Zentiva MG, 80mg*30
Acetylsalicylic acid Teva MG, 100mg*30
Zolpidem Bayer MG, 10mg*30
...

I need to remove the "MG" and the first word before the "MG", like this:

Telmisartan, 80mg*30
Acetylsalicylic acid, 100mg*30
Zolpidem, 10mg*30

Is this possible? I tried searching the forum but I can't find a solution, is there any formula or vba I can use to achieve this? Thank you
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Here's a user-defined function (UDF) you can try after you install it (see the example below for use).
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Function MG(S As String) As String
Dim V As Variant, i As Long
V = Split(Replace(S, ",", ""), " ")
For i = LBound(V) To UBound(V)
    If V(i) = "MG" Then
        V(i) = ""
        V(i - 1) = ""
        V(i - 2) = V(i - 2) & ","
    End If
Next i
MG = Application.Trim(Join(V, " "))
End Function
Excel Workbook
AB
1Telmisartan Zentiva MG, 80mg*30Telmisartan, 80mg*30
2Acetylsalicylic acid Teva MG, 100mg*30Acetylsalicylic acid, 100mg*30
3Zolpidem Bayer MG, 10mg*30Zolpidem, 10mg*30
Sheet6
 
Upvote 0
Here is another UDF that you can consider (see install instructions in Message #2 )...
Code:
Function MG(S As String) As String
  Dim Before As String, ArrMG As Variant
  ArrMG = Split(S, " MG")
  Before = Trim(ArrMG(0))
  ArrMG(0) = Left(Before, InStrRev(Before, " ") - 1)
  MG = Replace(Application.Trim(Join(ArrMG)), " ,", ",")
End Function
 
Upvote 0
Understanding that before "MG" there are always 2, 3 or more words, you can use the following formula:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:334.57px;" /><col style="width:310.81px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Telmisartan Zentiva MG, 80mg*30</td><td >Telmisartan, 80mg*30</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Acetylsalicylic acid Teva MG, 100mg*30</td><td >Acetylsalicylic acid, 100mg*30</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">Zolpidem Bayer MG, 10mg*30</td><td >Zolpidem, 10mg*30</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">test test2 test3 test4 test5 MG, 20mg*50</td><td >test test2 test3 test4, 20mg*50</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",250),LEN(LEFT(A1,FIND(" MG",A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(" MG",A1)-1)," ",""))),250)) & TRIM(MID(A1,FIND(" MG",A1)+3,LEN(A1)))</td></tr></table></td></tr></table>
 
Last edited:
Upvote 0
Here is another UDF that you can consider (see install instructions in Message #2 )...
Code:
Function MG(S As String) As String
  Dim Before As String, ArrMG As Variant
  ArrMG = Split(S, " MG")
  Before = Trim(ArrMG(0))
  ArrMG(0) = Left(Before, InStrRev(Before, " ") - 1)
  MG = Replace(Application.Trim(Join(ArrMG)), " ,", ",")
End Function
This is the same function with one code line and a variable removed...
Code:
Function MG(S As String) As String
  Dim ArrMG As Variant
  ArrMG = Split(S, " MG")
  ArrMG(0) = Left(S, InStrRev(Trim(ArrMG(0)), " ") - 1)
  MG = Replace(Application.Trim(Join(ArrMG)), " ,", ",")
End Function



Understanding that before "MG" there are always 2, 3 or more words, you can use the following formula:

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",250),LEN(LEFT(A1,FIND(" MG",A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(" MG",A1)-1)," ",""))),250)) & TRIM(MID(A1,FIND(" MG",A1)+3,LEN(A1)))
This slightly shorter formula seems to also work...

=SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,FIND(" MG",A1)))," ",REPT(" ",300)),300))&" MG","")
 
Last edited:
Upvote 0
Thank you all for your help. I have tested the solutions proposed and the best ones seem to be the formulas presented by Rick and DanteAmor I was not able to run JoeMo code and first solution presented by Rick because excel complains about this row of the code "MG = ...", the second UDF presented by Rick works but some cells have suffered some cuts. Here is a quick test:


Original cells from documentRick (with variable removed)Danteamor/Rick formula
Tromalyt, 150 mg x 28 cáps lib modTromalyt, 150 mg x 28 cáps lib#VALUE!
Paroxetina Mylan MG, 20 mg x 60 comp revParoxetina, 20 mg x 60 comp revParoxetina, 20 mg x 60 comp rev
Memantina Mylan MG, 10 mg x 28 comp revMemantina, 10 mg x 28 comp revMemantina, 10 mg x 28 comp rev
Xarelto, 15 mg x 42 comp revXarelto, 15 mg x 42 comp#VALUE!
Desloratadina Mylan MG, 5 mg x 20 comp revDesloratadina, 5 mg x 20 comp revDesloratadina, 5 mg x 20 comp rev
Faktu, 50/10 mg/g-50 g x 1 pda rect aplicadorFaktu, 50/10 mg/g-50 g x 1 pda rect#VALUE!
Vessel, 250 LSU x 60 cáps moleVessel, 250 LSU x 60 cáps#VALUE!
Dermovate, 0,5 mg/g-30 g x 1 pdaDermovate, 0,5 mg/g-30 g x 1#VALUE!
Atl Cr Hidra 100 GAtl Cr Hidra 100#VALUE!
Psodermil (100 mL), 0,5/20 mg/mL x 1 sol cutPsodermil (100 mL), 0,5/20 mg/mL x 1 sol#VALUE!
Wellion Calla Pl Tira Sangue Glic X 50Wellion Calla Pl Tira Sangue Glic X#VALUE!
Paroxetina Mylan MG, 20 mg x 60 comp revParoxetina, 20 mg x 60 comp revParoxetina, 20 mg x 60 comp rev
Metformina Mylan MG, 500 mg x 60 comp revMetformina, 500 mg x 60 comp revMetformina, 500 mg x 60 comp rev
Enalapril Hidroclorotiazida Bluepharma MG, 20/12,5 mg x 60 compEnalapril Hidroclorotiazida, 20/12,5 mg x 60 compEnalapril Hidroclorotiazida, 20/12,5 mg x 60 comp
Ã�cido AcetilsalicÃ*lico Aurovitas, 100 mg x 30 comp gastrorresistenteÃ�cido AcetilsalicÃ*lico Aurovitas, 100 mg x 30 comp#VALUE!
Vessel, 250 LSU x 60 cáps moleVessel, 250 LSU x 60 cáps#VALUE!
Paracetamol Bluepharma MG, 1000 mg x 20 compParacetamol, 1000 mg x 20 compParacetamol, 1000 mg x 20 comp
Telmisartan + Hidroclorotiazida Zentiva MG, 40/12,5 mg x 28 compTelmisartan + Hidroclorotiazida, 40/12,5 mg x 28 compTelmisartan + Hidroclorotiazida, 40/12,5 mg x 28 comp
Pradaxa, 110 mg x 60 cápsPradaxa, 110 mg x 60#VALUE!
Telmisartan + Hidroclorotiazida Zentiva MG, 40/12,5 mg x 28 compTelmisartan + Hidroclorotiazida, 40/12,5 mg x 28 compTelmisartan + Hidroclorotiazida, 40/12,5 mg x 28 comp
Sertralina Mylan MG, 50 mg x 60 comp revSertralina, 50 mg x 60 comp revSertralina, 50 mg x 60 comp rev
Tansulosina Mylan MG, 0,4 mg x 30 cáps lib prolTansulosina, 0,4 mg x 30 cáps lib prolTansulosina, 0,4 mg x 30 cáps lib prol
Pantoprazol Zentiva MG, 20 mg x 56 comp gastrorresistentePantoprazol, 20 mg x 56 comp gastrorresistentePantoprazol, 20 mg x 56 comp gastrorresistente

<colgroup><col style="mso-width-source:userset;mso-width-alt:15286;width:314pt" width="418"> <col style="mso-width-source:userset;mso-width-alt:12653; width:260pt" width="346" span="2"> </colgroup><tbody>
</tbody>

If we look at rows 10, 12 or 17 we can see some text being cutted with Rick proposal. I forgot to mention that some cells don't have the "MG" string, maybe that is causing some problems. However the formulas work great. I just want to know if we can add some kind of condition that avoid the #VALUE cells, if the condition we are looking for is not there then keep the original value. I tried a IF(ISNUMBER(SEARCH("MG"... approach but that wont work because the cells may contain "MG" and "mg". Thank you

<tbody>
</tbody>

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
Thank you all for your help. I have tested the solutions proposed and the best ones seem to be the formulas presented by Rick and DanteAmor

<tbody></tbody>

Try this:


=IFERROR(TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",250),LEN(LEFT(A1,FIND(" MG",A1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(" MG",A1)-1)," ",""))),250)) & TRIM(MID(A1,FIND(" MG",A1)+3,LEN(A1))),A1)
 
Upvote 0
I forgot to mention that some cells don't have the "MG" string, maybe that is causing some problems.
Yes, that was an important "forget". Here is my code modified to not cut text out when "MG" is not in the text...
Code:
Function MG(S As String) As String
  Dim ArrMG As Variant
  If InStr(S, " MG") Then
    ArrMG = Split(S, " MG")
    ArrMG(0) = Left(S, InStrRev(Trim(ArrMG(0)), " ") - 1)
    MG = Replace(Application.Trim(Join(ArrMG)), " ,", ",")
  Else
    MG = S
  End If
End Function

By the way, we can use my formula structure to produce a one-liner UDF like so...
Code:
[table="width: 500"]
[tr]
	[td]Function MG(Rng As Range) As String
  MG = Evaluate(Replace("SUBSTITUTE(@,"" ""&TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(@,SEARCH("" MG"",@&"" MG""))),"" "",REPT("" "",300)),300))&"" MG"","""")", "@", Rng.Address))
End Function[/td]
[/tr]
[/table]



However the formulas work great. I just want to know if we can add some kind of condition that avoid the #VALUE cells, if the condition we are looking for is not there then keep the original value.
Here is my formula modified to not produce that error for blank cells...

=SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,SEARCH(" MG",A1&" MG")))," ",REPT(" ",300)),300))&" MG","")
 
Last edited:
Upvote 0
Rick, your code now works great, thankyou, it was my fault. Thankyou also DanteAmor and JoeMo
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top