Find Strings that exclude certain words

I would like to find strings that exclude a short list of words - namely the brand names

So the macro would find anything except: apple, sony, asus and for those string(s) if there's any spaces add an _

The sample below shows several strings per row. All strings are contained within one cell. For example "Apple tom jones 456 1/12/12" is all contained within cell a1 & "Apple steve smith 525 2/12/12" within a2 etc. Numbers should be excluded from the search.

Below is the brand name, customer name, area code, date of purchase

Sample Data:
Apple tom jones 456 1/12/12
Apple steve smith 525 2/12/12
Sony JOE william johnson 262 5/12/12
Asus Carla Fausto 525 4/12/12

Desired Output:
Apple tom_jones 456 1/12/12
Apple steve_smith 525 2/12/12
Sony JOE_william_johnson 262 5/12/12
Asus Carla_Fausto 525 4/12/

any insight is appreciated thank you!

Try this

Please Login or Register  to view this content.

sorry i forgot to mention this is a snippet of the issue. there are several other strings like address, customer comments,etc that have their own unique issues to be addressed later where text to columns would cause more problems than it would solve.

Originally Posted by jindon

Try this

Please Login or Register  to view this content.

i'm fairly new to vba is there any way you could explain a bit on this (or anyone else perhaps)? the only thing i seem to understand is the do while loop. ive never seen the create object/ .pattern before, very interesting though i might say.

Originally Posted by Mac_Ro

sorry i forgot to mention this is a snippet of the issue. there are several other strings like address, customer comments,etc

Originally Posted by Mac_Ro

i'm fairly new to vba is there any way you could explain a bit on this?

I don't think the code will work for what you have.
I just wrote the code on the basis of your example data and the result.