+ Reply to Thread
Results 1 to 4 of 4

delete cells that contain certain words

  1. #1
    Registered User
    Join Date
    07-01-2015
    Location
    United States
    MS-Off Ver
    Microsoft office standard 2007
    Posts
    52

    delete cells that contain certain words

    i have a column of data in column A. I want a formula that will output the same date in column G. But i want all cells that include certain words to not be included. these words are in a cell on a different sheet. please let me know any possible solutions.
    Thanks!!!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: delete cells that contain certain words

    Attach a sample workbook (the FAQ describes how to) - it will be easier to see how your data is laid out and what you want to achieve.

    Pete

  3. #3
    Registered User
    Join Date
    07-01-2015
    Location
    United States
    MS-Off Ver
    Microsoft office standard 2007
    Posts
    52

    Re: delete cells that contain certain words

    column A is my data
    everything in yellow is my desired outcome
    and the blue is the words that if column A contain them i do not want them to be copied

    I want either a macro or formula that will do this.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: delete cells that contain certain words

    Insert a new row 1 at the top of your data, then you can put this formula in B2:

    =IF(A2="","",IF(SUMPRODUCT(--(ISNUMBER(SEARCH(J$2:J$3,A2)))),"",MAX(B$1:B1)+1))

    Copy this down as far as you like, but at least unto the bottom of your data. Then to get your reduced list you can use this formula (say in D2):

    =IF(ROWS($1:1)>MAX(B:B),"",INDEX(A:A,MATCH(ROWS($1:1),B:B,0)))

    and copy this down until you start to get blanks.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Delete words within phrase from list of words
    By El Cosmos in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-04-2014, 02:18 PM
  2. Delete multiple cells that contains words
    By gilboston2013 in forum Excel General
    Replies: 1
    Last Post: 08-09-2013, 10:01 AM
  3. [SOLVED] Delete repeated words in every cells in a Range
    By mmtoure in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-14-2013, 07:02 AM
  4. Replies: 3
    Last Post: 11-28-2012, 04:00 AM
  5. Delete blank row above cells with specific words.
    By beyre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2012, 02:17 AM
  6. [SOLVED] Delete cells in a column between cells that have key words in them.
    By bh36303 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2012, 10:01 AM
  7. Delete a Row if a cells contains specific words
    By jsolder in forum Excel General
    Replies: 1
    Last Post: 08-14-2009, 05:36 AM

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