Excel multiple FIND formula

microsoft excel

I am no expert with excel so any help is appreciated!

I am trying to make one cell do multiple searches within another.

I know that to search one cell for a specific word you would use
=FIND(Blue,C2)

But I need to find multiple different words in one cell. Heres an example of what im trying to do:

heres an example of what im trying to do

I need to show all the allergens in the ingredients cell eg peanuts, gluten, milk, sesame seeds etc

How do I get the result to list all the allergens?

I know that I can do a single find function in separate cells and then concatenate them, but there must be a more simple way?

Thanks

Best Answer

I would like to suggest Two possible methods. 1st is Formula and 2nd is MACRO.

Method 1:

enter image description here

=IFERROR(IF(OR(SEARCH(B1,A1)>0,SEARCH(C1,A1)>0,SEARCH(D1,A1)>0,SEARCH(E1,A1)>0),"Found",""),"Nfound")

Note:

  • Write this Formula in cell G1 and drag it Down.
  • You can extend the Formula, if more Text/Words to be searched.

Method 2:

enter image description here

  • At the Active Sheet press Alt+F11 to open VB Editor.
  • Copy & Paste below show code as Standard Module.
  • Select the Range A1:A3, where you want to search Text/Words to highlight.
  • RUN the Macro.
  • Enter Words in INPUT BOX, separated by Comma and press OK

Macro will Bold Face the words been found in Range A1:A3.

Sub MultiFindNBoldFace()

Dim strSearch As String
Dim arySearch As Variant
Dim searchRng As Range
Dim cel As Range
Dim i As Long, ii As Long


Set searchRng = Application.Selection
strSearch = InputBox("Please enter the text to make bold as a comma delimited list (Abc,Xyz) - No spaces:", "Bold Text")
If strSearch = "" Then Exit Sub
arySearch = Split(strSearch, ",")
For Each cel In searchRng

With cel

.Font.Bold = False
For ii = LBound(arySearch) To UBound(arySearch)

i = InStr(cel.Value, arySearch(ii))
If i > 0 Then

.Characters(i, Len(arySearch(ii))).Font.Bold = True
End If
Next ii
End With
Next cel

End Sub

You can adjust cell references in the Formula as well as in the Macro also.