Return values from string that match defined range within a single cell

[RaydenUK]

How can I search through a string and return only the values that are in a defined named range within a singe cell? Ex: assume cell A1 contains the string "blue_yellow_red_orange". However, I want cell B2 to return only those substrings that are present in my named range "Colors", which contains yellow, orange, and purple. Therefore, the output for B2 should be "yellow, orange".

 

[Aladin Akyurek]

In B1 control+shift+enter, not just enter:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH("_"&Colors&"_","_"&A1&"_")),Colors,""))

 

[RaydenUK]

Thank you for the reply but I am using excel 2010 and there is no TEXTJOIN function. Is there an alternative to this?

 

[Aladin Akyurek]

Thank you for the reply but I am using excel 2010 and there is no TEXTJOIN function. Is there an alternative to this?

Control+shift+enter, not just enter:

=REPLACE(aconcat(IF(ISNUMBER(SEARCH("_"&Colors&"_","_"&A1&"_")),","&Colors,"")),1,1,"")

For this formula to work, add the following code for ACONCAT as a Module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[RaydenUK]

That works!!! Thank you. You are greatly appreciated!

 

[Aladin Akyurek]

That works!!! Thank you. You are greatly appreciated!

Glad to help.