Reputation: 836
I have a list of substrings like this (list 1)
ABC
DEF
GHI
JKL
Then I have a list of strings like this (list 2)
ABCLKAKJDAKSDJ
DEFADLKASDKAS
GHIASDKASLDK
I want to find the list of substrings for each item in the list of strings, with an output like this:
ABCLKAKJDAKSDJ ABC
DEFADLKASDKAS DEF
GHIASDKASLDK GHI
You can assume that the strings in list 1 will only be found in the front of strings in list 2.
Upvotes: 0
Views: 537
Reputation: 15328
Try this
=query(arrayformula(split(flatten(iferror(C1:C3 &"~"& A1:A4 & "~" & SEARCH( A1:A4,transpose(C1:C3)))),"~",false)),"select Col1,Col2 where Col3=1")
List 1 in A
List 2 in C
Formula in E1
Upvotes: 1