Google Sheets Formula Returning Unfiltered Results: How to Fix?

Other Questions Google Sheets
1

Hey everyone, I’m stuck with a Google Sheets problem. My formula is giving me all the results instead of just the one I need.

Here’s what’s happening:

I’ve got a cell (C10) where I input a code like AC/12345. The sheet has data in this format:
#CLIENTCODE#12345_DATE_BARCODE_Bread#67891_DATE_BARCODE_Wine#12345_DATE_BARCODE_Tea

When I put in AC/12345, I want to see:

  • Bread
  • Tea

But I’m getting:

  • Bread
  • Wine
  • Tea

Same thing happens with AC/67891. It should just show Wine, but it’s listing everything.

I’ve tried using a complex formula with REGEXEXTRACT, MAP, and FILTER, but it’s not working as expected. The formula is pretty long, so I won’t paste it here.

What I need is a way to make the formula return only the items linked to the specific code I input. Any ideas on how to fix this? Thanks!

2

I’ve encountered a similar issue before, and I found that using a combination of SPLIT() and ARRAYFORMULA() can be quite effective. Here’s what worked for me:

First, I’d split the data into separate rows using SPLIT(). Then, I’d use ARRAYFORMULA() with a REGEXEXTRACT() to pull out the client codes and item names. Finally, I’d apply a FILTER() to match the input code.

Something like this might work:

=FILTER(
ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1, “#”), “(\d+)_(.*?)$”)),
ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1, “#”), “(\d+)”) ) = RIGHT(C10, LEN(C10)-3)
)

This assumes your data is in A1 and the input code is in C10. You might need to adjust the cell references.

The beauty of this approach is that it’s dynamic and should work with any number of items in your data string. Give it a shot and let me know if you need any tweaks!

3

hey soaringeagle, sounds like a tricky one! have u tried using SPLIT() to break up the string first? then maybe u could use FILTER() or QUERY() to grab just the parts matching ur input code. might be simpler than a complex regex. lemme know if u want me to throw together a quick example!

4

I’ve dealt with similar issues in Sheets before. Have you considered using REGEXEXTRACT in combination with SPLIT? Here’s a potential solution:

=ARRAYFORMULA(FILTER(REGEXEXTRACT(SPLIT(A1, “#”), “\d+.*?(.*?)$”), REGEXEXTRACT(SPLIT(A1, “#”), “^\d+”) = RIGHT(C10, LEN(C10)-3)))

This formula first splits your data string, then extracts the item names and client codes separately. It filters the results to match your input code.

Make sure A1 contains your data string and C10 has your input code. You might need to adjust cell references.

If this doesn’t work perfectly, let me know and I can help refine it further.