Odtnhj

I am using Excel for Mac 2016.

In column B there are 1.000+ product names, alphanumeric. One part of the product name can be the name of a color.

I now want to separate the color name from the product name and move the color name to column C, replacing it in column B with "" (empty).

In column A I have listed all color names that can appear in the product name.

My problem is to have the list of color names in A to look for as a substring in B. I tried arrays and ranges with SEARCH and FIND but only the first color is matched.
I have tried =FIND(26,29,B2) and =FIND(A1:A10;B2)

Hope to find help here.

Sample data:

column a:

black

yellow

red


column b:

first product red

second product black 2018

third product 2017

edit
After using the provided formula from below I changed it to get only the color in a separate field. I am using this:

Public Function getColor(rngSource As Range, rngReplace As Range, strDelim As String) 
As String Dim varInput 
Dim rngFound As Range varInput = Split(rngSource.Value, strDelim) 
For i = LBound(varInput) To UBound(varInput) 
Set rngFound = rngReplace.Find(varInput(i)) 
Next i 
getColor = Application.Trim(rngFound) End Function

It works fine, but when no color is found in a field I do not get EMPTY but an error. How do I tell the code that empty is also ok as a result?

I am not sure about Mac Environment but this is standard Excel formula which should work.

Setup :

Color Names in Cell : A1:A3

Source data begins from cell : B1

Formula for cell B1 data :

=TRIM(SUBSTITUTE(" "&B1&" "," "&IFERROR(LOOKUP(2^15,SEARCH(" "&$A$1:$A$3&" "," "&B1&" ",1),$A$1:$A$3),"|")&" "," "))

Copy down as much as needed.

Edit

Just to give an idea, two word replacement formula will look like below:

=TRIM(SUBSTITUTE(SUBSTITUTE(" "&B1&" "," "&IFERROR(LOOKUP(2^15,SEARCH(" "&$A$1:$A$3&" "," "&B1&" ",1),$A$1:$A$3),"|")&" "," ")," "&IFERROR(LOOKUP(2^15,SEARCH(" "&$A$1:$A$3&" "," "&SUBSTITUTE(" "&B1&" "," "&IFERROR(LOOKUP(2^15,SEARCH(" "&$A$1:$A$3&" "," "&B1&" ",1),$A$1:$A$3),"|")&" "," ")&" ",1),$A$1:$A$3),"|")&" "," "))

It will become only unmanageable beyond this point if you intend to have it for three words.

UDF - User Defined Function is written using VBA. See below code which you can use as starting point:

Public Function ReplaceWords(rngSource As Range, rngReplace As Range, strDelim As String) As String
Dim varInput
Dim rngFound As Range
varInput = Split(rngSource.Value, strDelim)
For i = LBound(varInput) To UBound(varInput)
 Set rngFound = rngReplace.Find(varInput(i))
 If Not rngFound Is Nothing Then varInput(i) = ""
Next i
ReplaceWords = Application.Trim(Join(varInput, strDelim))
End Function