Trying to separate words from text

[adjustment]

Maybe someone can help me figure this out and hopefully it is a fun one for you guys to figure out.

I am trying to separate text from a long list of products. In the middle of the name is either the word "silver" or "gold" and then there is a jewelry type. The problem is that some of the titles can have one to three words before silver or gold and I need those words to stay together. I can't use text to columns because it will just separate each word and then there is a ton to clean up.

Examples:

Garnet Silver Ring
Red Garnet Silver Pendant
Large Red Gold Necklace

So I need to be able to group by either garnet, red garnet, etc. and also how many are rings or pendants, etc.

Anyone have any insight?

Thanks in advance!

 

[steve the fish]

Re: help trying to separate words from text

Whats in the cell you are pointing the formula to? And what is the other word?

 

[adjustment]

Re: help trying to separate words from text

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($A2);"silver";REPT(" ";99));"gold";REPT(" ";99));"gents";REPT(" ";99));(COLUMNS($A$2:A2)-1)*99+1;99))

This is what I put in there - the third word is "gents" - I am in germany - hence the semi-colons

 

[steve the fish]

Re: help trying to separate words from text

Sure but what is in cell A2?

 

[adjustment]

Re: help trying to separate words from text

this is what I mentioned at the start:

garnet silver ring
red garnet silver ring
red garnet gents silver ring
etc.

 

[steve the fish]

Re: help trying to separate words from text

Ok its because you have two of your keywords in the same cell. If you dragged it across one more cell it should show the right words.

 

[adjustment]

Re: help trying to separate words from text

thanks again! all clear.