1

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.

Community
  • 1
  • 1
AKKA
  • 165
  • 4
  • 15
  • Google sheets has the [REGEXMATCH](https://support.google.com/docs/answer/3098292) function which accepts full regular expressions. – chthonicdaemon Jun 28 '17 at 03:48
  • Which search are you talking about? The worksheet function Search() or the Search dialog that opens with Ctrl+F? – teylyn Jun 28 '17 at 03:57
  • @teylyn: I'm referring to the search dialog that opens up with CTRL+F. I'm also referring to the tiny search bar that exists in the top right hand corner of the GUI. They are both the same right? What about the `Search()` function, is it any different from the CTRL+F one? – AKKA Jun 28 '17 at 04:27
  • @chthonicdaemon: Google sheets does? Great I think I will open my spreadsheet in google sheets then! – AKKA Jun 28 '17 at 04:30
  • Yes, the Search function is totally different from the search box. The search box searches all cells in the sheet and jumps to the cell that has a match. The Search() function searches inside one cell and returns the position of the match as a number, if any. I'm really surprised that you marked the answer that addresses the Search() function and not the feature that you were asking about. – teylyn Jun 28 '17 at 09:03
  • @teylyn: I accepted Tim's response before your remark that these two search functions are different (I didn't know that they are different). Nonetheless, it is still helpful as it does answer my question partially, even though its not the exact search function I am referring to. – AKKA Jun 29 '17 at 11:56
  • @teylyn: If you have a better answer, I'd be happy to hear anything constructive you have to add, and would select it to be the correct the answer if so. – AKKA Jun 29 '17 at 11:57

1 Answers1

1

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • But my dataset is so large that its inefficient for me to individually pinpoint the specific names and to therefore anticipate the entire name and append the whitespace `\s` at the end for a search.... and if I only search for a single whitespace, then I would encounter many irrelevant results that do not have trailing whitespaces, but have spaces in between two names.... – AKKA Jun 28 '17 at 04:26
  • @AKKA Then if I were you, I'd look into using some VBA code. With VBA I think you get close to full regex support and you wouldn't have this problem. – Tim Biegeleisen Jun 28 '17 at 04:27
  • Have a look [here](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) for a discussion of regex in VBA. – Tim Biegeleisen Jun 28 '17 at 04:31
  • @AKKA How come you accepted this as the answer if your question is about the search dialog that opens with Ctrl-F? This answer does not cover any aspect of the search dialog. – teylyn Jun 28 '17 at 09:05