Vikas Singhal
Vikas Singhal

Reputation: 836

Find a string in a list of substrings (google sheets)

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

Answers (1)

Mike Steelson
Mike Steelson

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

enter image description here

Upvotes: 1

Related Questions