CODEWITHSUNDEEP
vbaexcelwildcard
AKKA
AKKA

Reputation: 165

Does regex not work in Excel search?

I am trying to search for trailing whitespaces in text cells in Excel. Knowing that Excel search accepts regex, I expected to leverage on the full feature set, but was surprised to find that some features do not seem to work.

For example, I have some cells with strings like ELUFA\s\s\s\s\s (note: in my excel sheet there is no \s, but just blank invisible whitespaces, after ELUFA, but I had to add these \s in here otherwise Stackoverflow would just remove these whitespaces and the string would just appear to be ELUFA) or NATION CONFEC.\s with trailing whitespaces.

I used the expression [A-Z.]{1}\s+$ into the excel search function expecting that it would return search results for these cells, but it does not, and just tells me that nothing is found.

However, what I find really funny is that Excel search is somehow able to interpret a regex like this A *. Using this expression, excel search does find for me only the ELUFA\s\s\s\s\s cells, and no other cells which do not match this regex.

Is there some kind of limitations as to what subset of the full REGEX that Excel search accepts? How do we get excel search to accept the full REGEX feature set as described here?

Thank you.

Upvotes: 1

Views: 4030

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

The Excel SEARCH() function does not support full regex. It actually only supports two wildcards, ? and *. From the documentation:

You can use the wildcard characters — the question mark (?) and asterisk (*) — in the find_text argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

If you want to match spaces then you will have to enter them as literals. Note that finding any amount of trailing spaces could be as simple as ELUFA\s, with one space at the end, because that would actually match one, or more than one, space.

Upvotes: 1

Related Questions