RegexMatch

 

Syntax: RegexMatch(Text, Pattern, ErrorMessage, CodePage, DateTimeFormatSpecification, NumberFormatSpecification, AutoFormat)

 

Equivalent Function Names: RM

 

Purpose: Function used to find all matches of a given regular expression pattern.

 

Parameters: 
 

1.Text - Text to be matched
 

2.Pattern - Regular expression pattern to be matched against the given text.

Note: Pattern should not be longer than 255 characters.

An excellent tutorial to regular expressions is available at http://regexone.com/, from which the following table comes from:

Regex Code

Description

abc…

Letters

123…

Digits

\d

any Digit

.

any Character

\.

Period

[abc]

Only a, b, or c

[^abc]

Not a, b, nor c

[a-z]

Characters a to z

[0-9]

Numbers 0 to 9

{m}

m Repetitions

{m,n}          

m to n Repetitions

*

Zero or more repetitions

+

One or more repetitions

?

Optional

\s

any Whitespace

^…$

Starts and ends

()

capture Group

(a(bc))

capture Sub group

(.*)

capture Variable content

(a|b)

Matchs a or b

\w

any Alphanumeric character

\W

any Non-alphanumeric character

\d

any Digit

\D

any Non-digit character

\s

any Whitespace

\S

any Non-whitespace character

          
 

3.ErrorMessage - if specified, it's returned instead of the default exception text
 

4.CodePage - the page used for parsing of the matched results to numbers and dates - the default value is "en-US". The list of possible values is at http://msdn.microsoft.com/en-us/goglobal/bb896001.aspx (check the column Culture Name).

In case you encounter any problems, see this article.
 

5.DateTimeFormatSpecification - if this field is present, the CodePage parameter is ignored for the conversions of Dates to Excel format. See the full list at http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

Example: "Mon 23 Jun 8:45 AM 2013" will be recognized with this specification "ddd dd MMM h:mm tt yyyy"
 

6.NumberFormatSpecification  - if this field is present, the CodePage parameter is ignored for the conversions of Numbers to Excel format.

The parameter takes form of at least 4 characters:
1. Negative Sign character (1 character)
2. Currency Decimal Separator (1 character)
3. Currency Group Separator (1 character)
4. Currency Symbol (at least 1 character)

Example: "-.,$" is the standard setting for the US.

 

7.AutoFormat - an optional flag signifying if the result should be automatically converted to a range and if the result should be automatically formatted as Excel Data Types
 

 

Output: The matches range of the results. WARNING: The result cells are automatically converted to the right Excel type, e.g. date type.

 

Examples:

1. Get all matches of a given pattern in a string

=RM("1111342=Adam%20Franco&348572=Bob%20Jones","(?:&|&)?([^=]+)=([^&]+)") returns all matches of the pattern (in this case 2 matches) in a given string as a range.

 

 


2. Separate the area code and local phone number

=RM("(79)8239399","\(([^\)]*)\)(.*)")

 

 

or without autoformat with =RM("(79)8239399","\(([^\)]*)\)(.*)",,,,FALSE)

 

 

 

 

Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017