RegexReplace

 

Syntax: RegexReplace(Text, Pattern, Replacement, CodePage, DateTimeFormatSpecification, NumberFormatSpecification)

 

Equivalent Function Names: RR

 

Purpose: Function used to replace a given pattern with the provided replacement using regular expressions

 

Parameters: 

 

1.Text - Text to be considered for replacement
 

2.Pattern - Regular expressions pattern
 

3.Replacement - Regular expression replacement
 

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.

 

 

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

 

NOTE: an excellent introduction to the Regular Expression Replacements is available at http://msdn.microsoft.com/en-us/library/ewy2t5e0.aspx.
 

Examples:

1. Remove extra whitespace 
=RR("This is   text with   far  too   much    whitespace", "\s+"," ")

 

 

2. Remove currency symbols

=RR("$16.32 12.19 £16.29 €18.29  €18,29","\p{Sc}*(\s?\d+[.,]?\d*)\p{Sc}*","$1")

 

 

3. Substitute the text before the match

=RR("aa1bb2cc3dd4ee5","\d+","$'")

 

 

4. Flip the order of words

=RR("John Smith, 385 Central Ave., Cincinnati, OH, 45238","([A-Za-z]+) ([A-Za-z]+), (.*), (.*), (.*), ([0-9]+)","$6, $2, $1, $3,$4, $5") changes "John Smith, 385 Central Ave., Cincinnati, OH, 45238" into "45238, Smith, John, 385 Central Ave., Cincinnati, OH"

 

 

 

 

Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017