The following are the basics of using the Regex 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:

=Regex(StringToSearch, RegularExpression, IgnoreCase, GlobalSearch, ReturnMatchNumber, CapturingGroupNumber, DebugOutput)


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.
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).

ReturnMatchNumber is an integer representing the number of the match to return (1-n).

CapturingGroupNumber is an integer representing the number of the group to return (1-n) or the full match (0).



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

ReturnMatch

IgnoreCase

GlobalSearch

Return Value

abc

[aeiou]

 

 

 

a

abc123

\d

 

 

 

123

abc123

\w

 

 

 

abc

(987) 654-3210

\d+

0

1

1

987

(987) 654-3210

\d+

1

1

1

654

(987) 654-3210

\d+

1

1

2

3210

 

 

 

 

 

 



 

 

 




 

 

 




 

 

 




 

 

 





Created with the Personal Edition of HelpNDoc: Free Qt Help documentation generator