+ Reply to Thread
Results 1 to 9 of 9

remove cell contents if contains certain word in a specific column

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    71

    remove cell contents if contains certain word in a specific column

    I have this sub which makes the cell blank if the word "text" is found in it. This works across all columns.

    Sub ClearCellsContainingText()
    Do Until Cells.Find(What:="text", After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False) _
    Is Nothing

    Cells.FindNext.Clear
    Loop
    End Sub

    Can anyone alter this one slightly or give a new one so the instruction only looks at a certain column
    For example: I want any cell in column b to become blank if it contains the word "text".

    If the word "text" is found in any other columns then those columns stay as they are.
    Last edited by picton2000; 03-30-2013 at 05:10 PM. Reason: Problem solved

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: remove cell contents if contains certain word in a specific column

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: remove cell contents if contains certain word in a specific column

    Please use code tags with your code

    This code looks for text in column B and can easily be changed to any column

    Please Login or Register  to view this content.
    Last edited by AB33; 03-30-2013 at 07:05 AM.

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,317

    Re: remove cell contents if contains certain word in a specific column

    Hi there,

    See how this goes (thought initially on a copy of your data in case the results are not as expected):

    Please Login or Register  to view this content.
    HTH

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: remove cell contents if contains certain word in a specific column

    Or

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: remove cell contents if contains certain word in a specific column

    thanks andy. It works if the cell is singularly the word "text" and nothing else.

    If I have a cell which is something like "old text" or "type of text" then I want those cells empty as well.
    As long as the cell contains "text" either on its own or with other words then I need the cell emptied.

    Thanks

  7. #7
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: remove cell contents if contains certain word in a specific column

    both your suggestions work, but only if the cell is exactly equal to the word "text"
    I want the cell completely emptied as long as the cell contains text, such as "old text" or "what type of text"

    thanks for your help

  8. #8
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: remove cell contents if contains certain word in a specific column

    If you are referring to my code

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-27-2013
    Location
    Wiltshire, England
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: remove cell contents if contains certain word in a specific column

    Thanks, placing (the stars) *text* either side of the key word made it work just as I wanted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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