CODEWITHSUNDEEP
excellistsearchexcel-formulasubstring
sebke CCU
sebke CCU

Reputation: 183

Search substring in a list and give all the matching results comma separated

I have a list of items

My uncle eats a banana
My uncle eats a pear
My father eats an apple
My father eats a pear

If i enter the substring "uncle" i want to see "My uncle eats a banana,My uncle eats a pear" If enter "pear" I want to see "My uncle eats a pear,My father eats a pear"

The tricky thing is that it cannot be a macro

Any ideas how I can get that to work?

I found on https://www.xelplus.com/return-multiple-match-values-in-excel/

This formula: =TEXTJOIN(",",TRUE,IF(A5:A14=G4,B5:B14,""))

which works only for exact matches, not substrings

Upvotes: 0

Views: 113

Answers (2)

M. Ramezani
M. Ramezani

Reputation: 11

As I'm using Excel 2016 (without TEXTJOIN function), My method while trying not to use VBA was like this excel file:

https://www.dropbox.com/s/7c09ykr4redog4k/Concat%20without%20VBA.xlsx?dl=1

Hope helps you

Upvotes: 1

JvdV
JvdV

Reputation: 75840

You could use:

enter image description here

Formula in C1:

=TEXTJOIN(", ",,FILTER(A$1:A$4,ISNUMBER(FIND(" "&B1&" "," "&A$1:A$4&" "))))

Or:

=TEXTJOIN(", ",,IF(ISNUMBER(FIND(" "&B1&" "," "&A$1:A$4&" ")),A$1:A$4,""))

The 2nd option is to be CSE-entered in Excel-2019.

Upvotes: 2

Related Questions