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




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 (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

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


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




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