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

Presuming its just gold or silver then:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(LOWER($A1),"silver",REPT(" ",99)),"gold",REPT(" ",99)),(COLUMNS($A$1:A1)-1)*99+1,99))

Copy across one cell then down.

 

[adjustment]

Re: help trying to separate words from text

Presuming its just gold or silver then:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(LOWER($A1),"silver",REPT(" ",99)),"gold",REPT(" ",99)),(COLUMNS($A$1:A1)-1)*99+1,99))

Copy across one cell then down.

Wow - may I ask one other task? - how would you reverse this to take the word AFTER silver & gold? This formula is miles beyond me.

and thank you thank you thank you.

 

[steve the fish]

Re: help trying to separate words from text

Like i say you just drag it across to get the words after.

 

[adjustment]

Re: help trying to separate words from text

thanks again - like magic.

Okay. last one - I promise

There is one other word - I can work around it but - can I use this for 3 words? I tried to add it but it said there were too many arguments.

 

[steve the fish]

Re: help trying to separate words from text

Just nest another SUBSTITUTE in:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($A2),"silver",REPT(" ",99)),"gold",REPT(" ",99)),"whatever",REPT(" ",99)),(COLUMNS($A$1:B2)-1)*99+1,99))

Replace "whatever" with your word. Make sure its lower case.

 

[adjustment]

Re: help trying to separate words from text

I don't even know what to say - thank you seems so small. I really appreciate your help!

 

[adjustment]

Re: help trying to separate words from text

sorry to bug you again - it seems for fields with the 3rd word, it spits out what is before but not after it. The fields are blank.

 

[steve the fish]

Re: help trying to separate words from text

Sorry i copied wrong formula this end:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($A1),"silver",REPT(" ",99)),"gold",REPT(" ",99)),"whatever",REPT(" ",99)),(COLUMNS($A$1:A1)-1)*99+1,99))

 

[adjustment]

Re: help trying to separate words from text

thanks - I was able to fix that
But it still only spits out what is after the quoted terms for the first 2 - my third term comes back blank in the 2nd column