The following are the basics of using the RegexReplace UDF


This function allows the use of Regular Expressions in Excel. Regular Expressions are a way to perform filtering that can be far more precise than just using the wildcards '?' and '*'. The subject of regular expressions is vast, so it is not the intention to describe all the Regular Expression characters here. I suggest that the user search Google for 'Regular Expressions' and take a look at the vast amount of info that is available.

The syntax of the function is:

=RegexReplace(StringToSearch, RegularExpression, ReplacementString, IgnoreCase, GlobalSearch)


where

StringToSearch can be a string ("String to be searched.") or cell reference (A1) representing the data to be filtered.

RegularExpression can be a string ("\d") or cell reference (A2) representing the filter pattern.

ReplacementString can be a string ("\d") or cell reference (A2) representing the value to replace the search string.
IgnoreCase is a boolean that determines if the search is case sensitive (1 = Ignore case, 0 = Do not ignore case).
GlobalSearch is a boolean that determines if the search should be global (1 = Global, 0 = Not global).



The replacement string can include the following special replacement patterns:

Pattern        Inserts

$$        Inserts a "$".

$&        Inserts the matched substring.

$`        Inserts the portion of the string that precedes the matched substring.

$'        Inserts the portion of the string that follows the matched substring.

$n or $nn        Where n or nn are decimal digits, inserts the nth parenthesized submatch string as a back reference.



Metacharacters

^

Start of string

$

End of string

.

Any character (except newline (\n)

|

Alternate (OR)

[...]

Explicit characters to match

(...)

Logical grouping of parts of the expression

*

0 or more of the previous expression

+

1 or more of the previous expression

?

0 or 1 of the previous expression

\

In front of one of the characters above, it makes it literal instead of a special character


Character Classes

.

Matches any character except \n

[aeiou]

Matches any single character included in the set of characters

[^aeiou]

Matches any single character not included in the set of characters

[0-9a-fA-F]

A hypen (-) represents a contiguous character range

\w

Matches any word character. Equivelent to [a-zA-Z_0-9]

\W

Matches any nonword character. Equivelent to [^a-zA-Z_0-9]

\d

Matches any decimal digit. Equivalent to [0-9]

\D

Matches any non digit. Equivalent to [^0-9]


Examples

StringToSearch

RegularExpression

ReplacementString

IgnoreCase

GlobalSearch

Return Value

(987) 654-3210

\(\d+\)

(555)

0

1

(555) 654-3210



 

 

 




graphic

Created with the Personal Edition of HelpNDoc: Easily create HTML Help documents