Results 1 to 10 of 10

Thread: remove specific words in a cell

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default remove specific words in a cell

    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

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,449
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: remove specific words in a cell

    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
    Sheet6

    AB
    1Telmisartan Zentiva MG, 80mg*30Telmisartan, 80mg*30
    2Acetylsalicylic acid Teva MG, 100mg*30Acetylsalicylic acid, 100mg*30
    3Zolpidem Bayer MG, 10mg*30Zolpidem, 10mg*30

    Spreadsheet Formulas
    CellFormula
    B1=MG(A1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,209
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: remove specific words in a cell

    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mťxico
    Posts
    6,187
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: remove specific words in a cell

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

     AB
    1Telmisartan Zentiva MG, 80mg*30Telmisartan, 80mg*30
    2Acetylsalicylic acid Teva MG, 100mg*30Acetylsalicylic acid, 100mg*30
    3Zolpidem Bayer MG, 10mg*30Zolpidem, 10mg*30
    4test test2 test3 test4 test5 MG, 20mg*50test test2 test3 test4, 20mg*50

    CellFormula
    B1=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)))
    Last edited by DanteAmor; Jun 20th, 2019 at 12:24 AM.
    Regards Dante Amor

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,209
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: remove specific words in a cell

    Quote Originally Posted by Rick Rothstein View Post
    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


    Quote Originally Posted by DanteAmor View Post
    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 by Rick Rothstein; Jun 20th, 2019 at 01:48 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove specific words in a cell

    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 document Rick (with variable removed) Danteamor/Rick formula
    Tromalyt, 150 mg x 28 c√°ps lib mod Tromalyt, 150 mg x 28 c√°ps lib #VALUE!
    Paroxetina Mylan MG, 20 mg x 60 comp rev Paroxetina, 20 mg x 60 comp rev Paroxetina, 20 mg x 60 comp rev
    Memantina Mylan MG, 10 mg x 28 comp rev Memantina, 10 mg x 28 comp rev Memantina, 10 mg x 28 comp rev
    Xarelto, 15 mg x 42 comp rev Xarelto, 15 mg x 42 comp #VALUE!
    Desloratadina Mylan MG, 5 mg x 20 comp rev Desloratadina, 5 mg x 20 comp rev Desloratadina, 5 mg x 20 comp rev
    Faktu, 50/10 mg/g-50 g x 1 pda rect aplicador Faktu, 50/10 mg/g-50 g x 1 pda rect #VALUE!
    Vessel, 250 LSU x 60 c√°ps mole Vessel, 250 LSU x 60 c√°ps #VALUE!
    Dermovate, 0,5 mg/g-30 g x 1 pda Dermovate, 0,5 mg/g-30 g x 1 #VALUE!
    Atl Cr Hidra 100 G Atl Cr Hidra 100 #VALUE!
    Psodermil (100 mL), 0,5/20 mg/mL x 1 sol cut Psodermil (100 mL), 0,5/20 mg/mL x 1 sol #VALUE!
    Wellion Calla Pl Tira Sangue Glic X 50 Wellion Calla Pl Tira Sangue Glic X #VALUE!
    Paroxetina Mylan MG, 20 mg x 60 comp rev Paroxetina, 20 mg x 60 comp rev Paroxetina, 20 mg x 60 comp rev
    Metformina Mylan MG, 500 mg x 60 comp rev Metformina, 500 mg x 60 comp rev Metformina, 500 mg x 60 comp rev
    Enalapril Hidroclorotiazida Bluepharma MG, 20/12,5 mg x 60 comp Enalapril Hidroclorotiazida, 20/12,5 mg x 60 comp Enalapril 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 mole Vessel, 250 LSU x 60 c√°ps #VALUE!
    Paracetamol Bluepharma MG, 1000 mg x 20 comp Paracetamol, 1000 mg x 20 comp Paracetamol, 1000 mg x 20 comp
    Telmisartan + Hidroclorotiazida Zentiva MG, 40/12,5 mg x 28 comp Telmisartan + Hidroclorotiazida, 40/12,5 mg x 28 comp Telmisartan + Hidroclorotiazida, 40/12,5 mg x 28 comp
    Pradaxa, 110 mg x 60 c√°ps Pradaxa, 110 mg x 60 #VALUE!
    Telmisartan + Hidroclorotiazida Zentiva MG, 40/12,5 mg x 28 comp Telmisartan + Hidroclorotiazida, 40/12,5 mg x 28 comp Telmisartan + Hidroclorotiazida, 40/12,5 mg x 28 comp
    Sertralina Mylan MG, 50 mg x 60 comp rev Sertralina, 50 mg x 60 comp rev Sertralina, 50 mg x 60 comp rev
    Tansulosina Mylan MG, 0,4 mg x 30 c√°ps lib prol Tansulosina, 0,4 mg x 30 c√°ps lib prol Tansulosina, 0,4 mg x 30 c√°ps lib prol
    Pantoprazol Zentiva MG, 20 mg x 56 comp gastrorresistente Pantoprazol, 20 mg x 56 comp gastrorresistente Pantoprazol, 20 mg x 56 comp gastrorresistente

    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

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mťxico
    Posts
    6,187
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: remove specific words in a cell

    Quote Originally Posted by pdjm12 View Post
    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
    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)
    Regards Dante Amor

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,209
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: remove specific words in a cell

    Quote Originally Posted by pdjm12 View Post
    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:
    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


    Quote Originally Posted by pdjm12 View Post
    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 by Rick Rothstein; Jun 20th, 2019 at 10:00 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Jun 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: remove specific words in a cell

    Rick, your code now works great, thankyou, it was my fault. Thankyou also DanteAmor and JoeMo

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    Mťxico
    Posts
    6,187
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    14 Thread(s)

    Default Re: remove specific words in a cell

    Quote Originally Posted by pdjm12 View Post
    Rick, your code now works great, thankyou, it was my fault. Thankyou also DanteAmor and JoeMo
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •