I thought I’d add regular expressions to my Quick Find utility. I’ve used regular expressions about a dozen times in my life, so why not clutter up a simple tool with it. Now if I start my search string with rx: it will evaluate the rest as a regular expression.
My problem is when the pattern is invalid, like while I’m typing it in. For instance, when I type rx:([, the start of the above regular expression, it returns every cell in the workbook because I’ve ignored errors in the pattern matching. I suppose I could make it show no results instead of everything. Or maybe people who type in regular expressions will just expect crappy results until they’re done.
The above regular expression is supposed to match US phone numbers that are formatted like (999) 999-9999. Here’s how I think it works:
| ( | matches an open paren. Since it’s a special character, it needs the backslash to escape its specialness |
| [2-9] | match any digit 2 through 9 |
| d{2} | d means digit and {2} means 2, so match two consecutive digits |
| ) | Same as the open paren |
| space | match a space |
| [2-9]d{2} | same as above – match three digits where the first is between 2 and 9 |
| – | match a dash |
| d{4} | match four consecutive digits |
I’m going to set up a special page for utilities and I’ll post the update add-in next week.
P.S. I realize I got the phone number wrong, but it wasn’t worth a new screen shot.
In the textbox change event, if the text begins with “r”, “rx”, or “rx:”, have it skip the update, but display a small button next to the textbox that says “Go”.
PS. The small light gray text on white in your table is illegible.
This could be very useful. I wonder if putting a checkbox on the form and then if the option is checked, show nothing until there is something to show. The checkbox also allow, in the very rare instance, the user to search for rx:.
Currently, it does not display real-time if there are over half a million cells. In that case it only displays when you exit the textbox. Maybe a ‘go’ button would be clearer. And I could use the same thing for regex – just act like it’s too many cells.
What browser do you use Jon? It’s clear for me on FF and IE. Maybe my eyes are only 40 years old. :) I’ll check the style sheet and fix it up.
Didn’t see anything for font-color, but changed from .9 to 1.2 em. Looks better to me, but let me know what you think.
Love this tool. I’ve always thought MS software would benefit from better-integrated RegEx support. I’m surprised you say you have used them a dozen times in your life; I probably use them a dozen times an hour.
Another suggestion for solving the incremental search vs. invalid-RegEx problem would be to require a RegEx to be formatted as in many text-oriented languages’ (AWK, Perl, Ruby, …) RegEx support, inside forward-slash delimeters like this:
/([2-9]d{2}) [2-9]d{2}-d{4}/
Given the first character typed is a slash, you could defer the incremental results until an unescaped trailing slash is seen.
Alternatively, you could add a checkbox for “show incremental results”, defaulting to checked, and let the user turn it off if they don’t want to see it. Many times, I build up RegEx’s in small parts as I hone in on what I’m really looking for, so seeing the intermediate results could actually be a feature.
Dick –
The font is perfectly legible now. The size has increased more than the ratio you mention, and it’s dark in color.