remove specific words in a cell

P

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
Rick Rothstein said:
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
Click to expand...
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



DanteAmor said:
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)))
Click to expand...
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:

[TABLE="width: 1765"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 1765"]
<tbody>[TR]
[TD]
Tromalyt, 150 mg x 28 cáps lib modTromalyt, 150 mg x 28 cáps lib
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
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
Vessel, 250 LSU x 60 cáps moleVessel, 250 LSU x 60 cáps
Dermovate, 0,5 mg/g-30 g x 1 pdaDermovate, 0,5 mg/g-30 g x 1
Atl Cr Hidra 100 GAtl Cr Hidra 100
Psodermil (100 mL), 0,5/20 mg/mL x 1 sol cutPsodermil (100 mL), 0,5/20 mg/mL x 1 sol
Wellion Calla Pl Tira Sangue Glic X 50Wellion Calla Pl Tira Sangue Glic X
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
Vessel, 250 LSU x 60 cáps moleVessel, 250 LSU x 60 cáps
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
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>
[TD="class: xl63, width: 418"]Original cells from document[/TD]
[TD="class: xl63, width: 346"]Rick (with variable removed)[/TD]
[TD="class: xl63, width: 346"]Danteamor/Rick formula[/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

[TD="align: center"]#VALUE![/TD]

</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
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
pdjm12 said:
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[TABLE="width: 1765"]
<tbody></tbody>[/TABLE]
Click to expand...

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
pdjm12 said:
I forgot to mention that some cells don't have the "MG" string, maybe that is causing some problems.
Click to expand...
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]



pdjm12 said:
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.
Click to expand...
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
You must log in or register to reply here.

Similar threads

Lacan
  • Question Question
How to extract records that contains a specific text words? 🙏🙌
2
Replies
10
Views
1K
Lacan
Lacan
SkywardPalm
  • Question Question
Best Match (Fuzzy Match Alternative) for Product Description and Pulling Respective Product ID Code
Replies
5
Views
691
Akuini
Akuini
C
  • Solved
Help to remove specific named duplicate word from a cell
2
Replies
11
Views
708
Cruiser69
C
PatHay
  • Solved
VBA Checkboxes (Forms)
Replies
3
Views
1K
PatHay
PatHay
S
  • Question Question
Pop Up Message Box - Out of Date items in a Column
Replies
1
Views
1K
GlennUK
G

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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