tmehta.com Home
regexp/index.htm
  VB(A) functions
  Using functions
  Examples
  Patterns
 
The RegExpSubstitute function

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ࢯss Pat is Johnࢯss
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஡tive 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 Replica Tag Heuer Watches applicable pattern would be cat|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 Replica IWC Watches .

9809abc123 9809
99999xyz 99999
Where the second column contains the formula =RegExpFind(F15,"\d*")