Search and return matches into multiple columns

[RowanW]

Hi All

I have a scenario where I'm attempting to extract certain words that appear between double quotes but only if they meet a certain condition.

For example I want to search for ALL fruit in column A and present these individually in the subsequent columns.

Fruit' = "Pear" OR 'Fruit' = "Apple" AND 'Vegetable' = "Cabbage" AND 'Meat' = "Beef"

So for the above, it would return "Pear" in Column B and Apple in Column C (ignoring all of the other items that are not fruit)

Any idea how I would go about this? (Ideally an excel formula as my skills in VBA are at level 1)

 

[KOKOSEK]

I've written UDF like this:

Function GetFruits(MyStr As String, MyEl As Long) As String
Dim temp1 As Variant
Dim temp2 As String
Dim i As Long
temp1 = Split(MyStr, " ")
For i = LBound(temp1) To UBound(temp1)
    If temp1(i) = "Fruit'" Or temp1(i) = "'Fruit'" Then temp2 = temp2 & temp1(i + 2) & ","
Next i
temp1 = Split(temp2, ",")
On Error GoTo MyError
GetFruits = Replace(temp1(MyEl - 1), """", "")
Exit Function
MyError:
GetFruits = ""
End Function

then into B2:

=GetFruits($A2,COLUMN()-1)

and drag it right/down as needed.

A​	B​	C​
Fruit' = "Pear" OR 'Fruit' = "Apple" AND 'Vegetable' = "Cabbage" AND 'Meat' = "Beef"	Pear	Apple

A​	B​	C​
Fruit' = "Pear" OR 'Fruit' = "Apple" AND 'Vegetable' = "Cabbage" AND 'Meat' = "Beef"	=GetFruits($A2,COLUMN()-1)	=GetFruits($A2,COLUMN()-1)