Is it Possible to Delete or Remove Words from a Column?

**randolph188** · 10-28-2009, 11:47 AM

Please could someone tell me if there is a formula for the following scenario -

I have a list of words in column A, each horizontal row could consit of one or multiple words, and the vertical length of the list of words could also vary.

I would like to either delete or remove a complete row, that contains certain word(s).

If the row does not contain the words I specifiy, the row would be deleted or another way - if the row contains the words I specifiy, the row would be moved to another column.

E.g.

In column A I have a list of words like this -

Car Insurance
House Insurance
Vehicle Insurance
Life Insurance
Car Auto Insurance

I would like all rows containing the words car ,(and/or) vehicle, auto to be removed and put in a separate column, or I would like any row not containing these words to be deleted from the list or these words could be put in another column, either option is good. In the above example the words Life Insurance and House Insurance would be deleted from the list or put in another column, or the words Car Insurance, Vehicle Insurance and Car Auto Insurance would be moved to another column.

There could be 1 or more words that I specify. (I am only interested in keeping the row of words that have words in them that I specify).

Please could someone tell me if this is possible? Thank you

Stephan

**Jack in the UK** · 10-28-2009, 01:04 PM

This might get you started and you can build on this - will remove one word

Please Login or Register  to view this content.

**randolph188** · 10-28-2009, 01:10 PM

Thanks for your reply Jack but what yo have written is completly new to me. Rather than removing is there no way of taking the words in the cell and moving them to another column like in my example? Thank you

**randolph188** · 10-28-2009, 01:45 PM

Is it possible to do this with the advanced filter feature?

**daniel_gudman** · 10-28-2009, 03:48 PM

Jack has posted an elegent VBA solution.

The problem as you've stated it requires a complex answer: deleting and moving entire rows around is not something excel likes to do and you have to make it do what you want.

What you might want to consider is having a second group, with "selected" values. So you have a list of input data in your first group, and a list of output data in the second group.

Based on what you're describing, I would suggest nesting text functions (I think search is ideal for this) inside of a logical function like IF that looks for your text input.