Ask Your Question

calc data advanced filter/ needs exact match to extract

asked 2019-01-25 17:31:42 +0200

old_farmer gravatar image

I have a database that has business names. An example would be The Shoe Store. If I put Shoe as my search data in the advanced filter I get nothing. I have to put The Shoe Store to get all the records for that company. I've tried (Shoe), *Shoe, ?Shoe, $Shoe and nothing works. Sometimes I only remember part of the name of a business or I didn't enter it fully.

Is there some way to use the advanced filter so that I can find records with multiple words but only put one word in the search?

I know that in some cases I could use Standard Filter and "contains" but it limits to only 3 conditions.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-01-25 20:18:34 +0200

updated 2019-01-25 22:55:12 +0200

Hello @old_farmer

Please go to menu Tools -> Options -> LibreOffice Calc -> Calculate and check if "Search criteria = and <> must apply to whole cells" If this is checked, only exact matches will be returned. Advanced filter also accepts Regular Expressions as filter criteria, so you can play with them too.

--- edit 2019/01/25 23:52 ---

Based on @Lupp comment tested (sample file) advanced filter with and without "Search criteria = and <> must apply to whole cells" checked.

  1. Option enabled (K1 cell is the start of the filter result range): criteria_enabled

  2. Option disabled (K1 cell is the start of the filter result range): criteria_disabled

It is the only option I changed between setting both filters, other options/criteria remains the same.

edit flag offensive delete link more


This setting controls the application of RegEx by the functions capable of doing so (SEARCH, MATCH, LOOKUP/VLOOKUP/HLOOKUP, COUNTIF/COUNTIFS and alike). It shouldn't afflict the working of filters and other tools which have their own sets of options.

Lupp gravatar imageLupp ( 2019-01-25 22:33:25 +0200 )edit

I was so strictly focused on the Regex part that I didn't even read thoroughly enough.

Lupp gravatar imageLupp ( 2019-01-25 23:15:18 +0200 )edit

Oh, it's just fine! :) RegEx is not my favorite thing and your answer on Regular expressions part is much more informative than mine, so is a great addition and completes the scope of this question.

SM_Riga gravatar imageSM_Riga ( 2019-01-25 23:36:04 +0200 )edit

Thank you. That worked for me. Really appreciated it as I've tried for hours to find an answer.

old_farmer gravatar imageold_farmer ( 2019-01-26 15:32:34 +0200 )edit

answered 2019-01-25 22:25:34 +0200

Lupp gravatar image

"I've tried (Shoe), *Shoe, ?Shoe, $Shoe ..." This kind of trial and error won't lead to trial and success.)

You need to enable the option Regular expression for the filter. Any name containing the substring "shoe" is then matchd by the RegEx .*shoe.* . Please read about regular xpressions (at least) in the help.
In addition full-grown filters have a setting for case-sensitivity.

(The "wildcard" characters used in MS software are a much less powerful surrogate for regular expressions. Though LibreOffice Calc has an option to enable usage of wildcards in formulae since V 5.2. filters never did.)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-25 17:31:42 +0200

Seen: 56 times

Last updated: Jan 25