Regular Expressions in Excel and VBA
Eugeny Sattler
This function replaces the contents of ReplaceIn by substituting every occurrence of ReplaceWhat with ReplaceWith. It could be something simple as replacing one word with another as in:
| Jim is John’s boss | Pat is John’s boss |
| Where the second cell contains the formula =RegExpSubstitute(A26,"Jim","Pat") | |
Or, it could be something a little more complicated as exchanging the first and last names and adding a comma as in:
| Jane Doe | Doe, Jane |
| Where the second cell contains the formula =RegExpSubstitute(A22,"(\S+)(\s+)(\S+)","$3,$2$1") | |
The RegExpFind function
This function returns every occurrence of FindWhat in the contents of FindIn. If there are multiple matches, it returns an array. Unlike Excel’s native search functions,RegExpFind returns the matched string itself, not the position of the match. This might not seem very useful until one realizes that the search token is not necessarily a literal but a pattern. Hence, the returned value is the actual text that corresponds to the pattern.
For example, if a cell contains information about whether someone has a cat or a dog as a pet and we need to know specifically which animal, the applicable pattern would becat|dog as in =RegExpFind(A29,"cat|dog") and it would yield either cat or dog.
A more powerful example is extracting all the leading numbers in a cell that contains some numbers followed by alphabetic text. The applicable pattern would be \d*. The \d is the shorthand of any number [0-9] and the * indicates zero or more occurrences of the preceding token, which happens to be a digit.
| 9809abc123 | 9809 |
| 99999xyz | 99999 |
| Where the second column contains the formula =RegExpFind(F15,"\d*") | |
Eugeny Sattler
Regex functionality in worksheet formulae was great but surely not enough for those who work with MS Excel 95% of their working time (like me)