+ Reply to Thread
Results 1 to 4 of 4

Removing Exact Text String from giving Excel Cells - Unexpected Behaviour

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Egypt
    MS-Off Ver
    Excel 2003
    Posts
    40

    Removing Exact Text String from giving Excel Cells - Unexpected Behaviour

    Hi,
    I'm using this Macro to remove predefined text values from selected Excel cells which contain multiple text values separated by commas. I'm targeting mostly any single letter words or prepositions. (a, on, to...etc) Sometimes these single letters come at the beginning of a cell (no space before), so I had to set the code to remove "a, " or "on, ". Doing so, when I run the code they get removed whether they come as single words or parts of other words.

    Example: Running the Macro over cells containing:
    A2: a, ticket, to, paris
    A3: propaganda, multiple, offers
    A4: on, mention, on, movies

    It produces:
    A2: ticket, paris
    A3: propagandmultiple, offers
    A4: mentimovies

    The Code:

    Dim cell As Range, MyStr As String

    Application.ScreenUpdating = False
    For Each cell In Selection
    MyStr = cell.Value
    MyStr = Replace(MyStr, "to, ", "")
    MyStr = Replace(MyStr, " to, ", "")
    MyStr = Replace(MyStr, "a, ", "")
    MyStr = Replace(MyStr, " a, ", "")
    MyStr = Replace(MyStr, "on, ", "")
    MyStr = Replace(MyStr, " on, ", "")
    cell.Value = MyStr
    Next cell
    Application.ScreenUpdating = True
    End Sub


    Any suggestions to get that working properly?

    many thanks

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Removing Exact Text String from giving Excel Cells - Unexpected Behaviour

    ghoneim,

    In the future, please wrap your code in code tags, link in my sig for how.
    As for your question, something like this should work for you:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-19-2012
    Location
    Egypt
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Removing Exact Text String from giving Excel Cells - Unexpected Behaviour

    Will keep that in mind.
    For the second time, thanks a lot Tigeravatar. I do appreciate your solutions

    Quote Originally Posted by tigeravatar View Post
    ghoneim,

    In the future, please wrap your code in code tags, link in my sig for how.
    As for your question, something like this should work for you:
    Please Login or Register  to view this content.

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Removing Exact Text String from giving Excel Cells - Unexpected Behaviour

    You're very welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1