calc data advanced filter/ needs exact match to extract

old_farmer · January 25, 2019, 4:31pm

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.

SM_Riga · January 25, 2019, 7:18pm

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.

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

criteria_enabled1220×680 102 KB
Option disabled (K1 cell is the start of the filter result range):

criteria_disabled1202×695 107 KB

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

Lupp · January 25, 2019, 9:33pm

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 · January 25, 2019, 10:15pm

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

SM_Riga · January 25, 2019, 10:36pm

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.

old_farmer · January 26, 2019, 2:32pm

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

Lupp · January 25, 2019, 9:25pm

“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.)