[Solved] If a cell contains mulitple instances of specific text, then extract top 3 of the specified text found in the cell

by


google-spreadsheet solution:

'for a CSV of all matches
=arrayformula(TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH(E2:E8, A2)), E2:E8, "")))
'for a CSV of the first three matches
=replace(arrayformula(TEXTJOIN(", ", TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, ""))), find("|", substitute(arrayformula(TEXTJOIN(", ", TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, ""))), ",", "|", 3)&"|||"), len(A3), "")

enter image description here

excel-2016textjoin solution:

'for a CSV of all matches input as array formula with ctrl+shift+enter
=TEXTJOIN(", ", TRUE, IF(ISNUMBER(SEARCH(E2:E8, A2)), E2:E8, ""))
'for a CSV of the first three matches input as array formula with ctrl+shift+enter
=replace(TEXTJOIN(", ", TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, "")), find("|", substitute(TEXTJOIN(", ", TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, "")), ",", "|", 3)&"|||"), len(A3), "")

3

solved If a cell contains mulitple instances of specific text, then extract top 3 of the specified text found in the cell