Extract simple string from array of complex cells

[Turk182]

Hello, I need help combining Excel functions to do 2 things:

1. Search for the SIMPLE food names (like BEEF

SALMON

CHICKEN) on my personal food list—sheet2 colA

WITHIN sheet1 colA’s COMPLEX
strings—(like

Shoulder roast, BEEF, visible fat eaten

Fish, wild-caught, SALMON, sockeye, pan-seared

CHICKEN, breast meat, Skinless, baked; and then

2. When the simple name is found, I need the
6-item contents of—sheet1 column B (100g 125 22 8 0 0)
to spill across the very next 6 columns (B:G) of sheet2, next to (same row as) the simple food name

Thx
I appreciate any potential solutions.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Turk182]

Sheet1

Sheet2

Thank you very much. Above are the range examples from each of the 2 spreadsheets. The Mr. Excel button is not working.

 

[mumps]

Assuming that your food names start on row 9 of Sheet2, try:

Sub ExtractString()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, food As Range, fnd As Range, portion As Variant
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With desWS
        For Each food In .Range("A9", .Range("A" & .Rows.Count).End(xlUp))
            Set fnd = srcWS.Range("A:A").Find(food, LookIn:=xlValues, lookat:=xlPart)
            If Not fnd Is Nothing Then
                portion = Split(fnd.Offset(, 1), " ")
                food.Offset(, 1).Resize(, 6).Value = Array(portion(0), portion(1), portion(2), portion(3), portion(4), portion(5))
            End If
        Next food
    End With
    Application.ScreenUpdating = True
End Sub

 

[Turk182]

Thanks mumps. My food names do start on row 9 of Sheet2. However, I was wondering if there might be solution using Excel functions/formulas....

 

[Fluff]

The Mr. Excel button is not working.

Have a look here XL2BB Icons greyed out

 

[Fluff]

How about

=TEXTSPLIT(XLOOKUP("*"&A9&"*",Sheet1!A2:A100,Sheet1!B2:B100,"",2)," ")