# calc data advanced filter/ needs exact match to extract

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 close merge delete ## 2 Answers Sort by » oldest newest most voted 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): 2. Option disabled (K1 cell is the start of the filter result range): It is the only option I changed between setting both filters, other options/criteria remains the same. more ## Comments 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. ( 2019-01-25 22:33:25 +0200 )edit Sorry! I was so strictly focused on the Regex part that I didn't even read thoroughly enough. Sorry. ( 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. ( 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. ( 2019-01-26 15:32:34 +0200 )edit "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.)

more