Is this Autofilter Search behavior documented anywhere?

[UniMord]

I've noticed that when I enter a search string in Autofilter's main Search box (not the Text Filters), and I end the string with *, it becomes the equivalent of "Begins with".
For example, 3 Main will match 123 Main, 23 Main, and 3 Main. But 3 Main* will only search for items beginning with 3 Main, and exclude 123 Main or 23 Main.

(This is not normal * behavior, as you can test for yourself in Autofilter's Text Filters.)

Is this documented anywhere? Why does it behave this way? Are there any other special characters that affect the Search box's behavior?

 

[RickXL]

Hi,

I expect it is using the standard Find and Replace rules: https://support.office.com/en-gb/ar...333dead6ae33?ui=en-US&rs=en-GB&ad=GB&fromAR=1

Regards,

 

[UniMord]

That's what I would expect as well, but, as far as I can tell, that's not what's happening. Yes, in Autofilter's Text Filters, the standard wildcards work normally. But, in the main Search box, wildcards don't work at all. The behavior that seems strange to me is, that when I end the search string with an asterisk, it treats the search as a "Begins with" search.

 

[RickXL]

OK, I see what you mean.

I must be strange because I thought it would work like Begins With but the Find box doesn't does it? It kind of assumes a leading asterisk.

Sorry, I have not seen another guide.

regards,

 

[Peter_SSs]

Perhaps I'm not understanding what you are saying but it makes sense to me.

When you begin typing in the box, Excel assumes a 'Contains' search, so "3 Main" does match all those items you mentioned.
As soon as you type the asterisk at the end ("3 Main*"), Excel effectively says "OK, this person wants to use wildcards & they haven't used one at the beginning, so they must want the 'Begins With' filter."

If you then move the insertion point back to the beginning and type

- another asterisk ("*3 Main*") then the filter again changes to a 'Contains' type & all items are returned.

- a question mark ("?3 Main*") then of the items you mentioned, only "23 Main" would be returned.

 

[UniMord]

You nailed it. That makes perfectly good sense. Thanks!

 

[Peter_SSs]

You are welcome.