Solved Multiple Exact Words In String in Excel

June 8, 2015 at 14:02:47
Specs: Windows 7

I have an Excel document, with raw data, which I need to organize into sub categories.

I need to find exacts words in column C and return the same words in Column F.

Is there a code which would allow me to do that?

For Example in Column C I have

Cars - Audi
Music - Audio
Audi - Q7

I would need all the cells with the word "Audi" to be classified as "Audi" in column F

Thanks :)

message edited by PegahKe

See More: Multiple Exact Words In String in Excel

Report •

June 8, 2015 at 14:12:54

Report •

June 9, 2015 at 06:38:59
I need to match about 10-15 words

Report •

June 9, 2015 at 06:47:04
✔ Best Answer
Just as a first pass suggestion, if you have more than a few strings to match (enough that would make a long Nested IF function cumbersome) you could use multiple columns (typically referred to as "Helper Columns") to extract each string and then Concatenate the results into Column F.

For example, if you wanted to extract Audi and Music in your example, you could do this:

Set up a sheet to look like this:

                C               F         G        H        
1            Raw Data        Category    Audi    Music
2            Cars - Audi
3            Music - Audio
4            Audi - Q7 

In G2, enter the following formula, then drag in down to G4, then over to H4

=IF(NOT(ISERROR(FIND(" "&G$1&" "," "&$C2&" "))),G$1,"")

This should extract the strings shown in G1:H1. You will note the leading and trailing spaces included the formula. This is done to ensure that "Audi" is extracted, but not "Audio".

Next, enter either one of these formulas in F2 and drag it down:




This will concatenate the values in G2 and H2, but since only one of those cells contains data, that is all that you will see in Column F.

Obviously, you will need a column for each string that you want to extract and a Concatenation formula that references all columns used. You do not have to put the Helper Columns in G and beyond, in fact they don't even need to be on the same sheet. You can use another sheet (and even Hide it) to hold those columns and then reference them with the Concatenation formula.

Let me know if that works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

June 9, 2015 at 06:54:43
It looks like our responses crossed paths, timing wise.

Take a look my "Helper Column" suggestion. I think it will work for you, but I might need to see some more example data (both input and desired output) to be sure.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

June 9, 2015 at 12:10:40
thank you so much for your help. I've incorporate the formula into my ,IF(ISNUMBER(SEARCH(

and it works

Report •

Report •

Ask Question