0

I have some data from which I'd like to return only cells that contain specific text.

In Row 1 I have a sequence of numbers, from 1 to 5, and in the row below each number a text string.

I want to FILTER Row 1 based on searching for text in the cells in Row 2.

  1. The search values consist of individual letter characters.
  2. Each cell I'm checking for the search values is made up of multiple text strings separated by commas. For example, b,dec10 or c,b,dec10
  3. A match is made if any one of the search values is found in the cell, but it must be a perfect match. For example,
    • b,dec10 and c,b,dec10 are both matches for b because b is a comma separated string in each of them.
    • c,b,dec10 is a match for c for the same reason.
    • b,dec10 is NOT a match for c because c does not exist as a complete comma delimited string.
    • r,nov12 and b,dec10 are matches for r and b because they each contain at least one of the lookup values.

Example Search

A B C D E
1 1 2 3 4 5
2 b,dec10 x,nov12 c,nov10 c,b,dec10 r,nov12

Using the sample data above, a search for b or x should return {1,2,4}

I have tried the following formula:

=IFERROR(
   FILTER(A1:E1,
     (A2:E2="b")+(A2:E2="x"))

It returns values if cells in A2:E2 contain b or x and nothing else, but doesn't return anything if there is anything else in the cell.

How should I modify my formula?

2
  • Is this in Google Sheets? Be sure to add the relevant tag.
    – Laurel
    Dec 16, 2023 at 23:19
  • I edited your question for clarity. If I misinterpreted anything please edit and correct.
    – Blindspots
    Dec 18, 2023 at 0:05

1 Answer 1

0

This is a good candidate for combining FILTER and REGEXMATCH

Formulae

   1.  Match b

=FILTER(A1:E1,
   REGEXMATCH(A2:E2, "(^|,)b(,|$)"))

Matches b if it is both:

  1. (^|,) - At the start of the string ^ or | preceded by a comma ,
  2. (,|$) - followed by a comma , or | at the end of the string $
    Formula matches b

   2.  Match b or x

=FILTER(A1:E1,
   REGEXMATCH(A2:E2, "(^|,)b|x(,|$)"))
  • same rules as before except checks for either b or x
    Formula matches b or x

   3.  Match Cell Value

=FILTER(A1:E1,
   REGEXMATCH(A2:E2, "(^|,)"&G2&"(,|$)"))
  • G2 can contain a single value b or multiple values b|x
    Formula uses a cell reference for the value(s)

   4.  Match Values From a Range

=FILTER(A1:E1,
   REGEXMATCH(A2:E2, 
     "(^|,)"&JOIN("|",G2:G3)&"(,|$)"))
  • Uses JOIN to concatenate a range of values with an or | operator as delimiter.
    Formula concatenates a range of values with an or operator


Function Support Links

FILTERJOINREGEXMATCH

2
  • 1
    Many thanks to @Blindspots for the suggestion, clarification and simplification. The formula you gave makes much more sense and easier to operate and modify than mine one, although I made my formula working after 3 days of experimenting :) as I use as criteria not "b" but "bb," or "cc,".. Dec 18, 2023 at 7:18
  • @GeorgiSlaveev I presume you are aware that the answer above supports bb or cc or any other string you want to use. I only used b in the example based on your question.
    – Blindspots
    Dec 20, 2023 at 3:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.