How to use Regex in Power Automate

Unfortunately, Power Automate does not have any support for Regular Expressions (regex or regExp), which can sometimes be very limiting, or make things which would be easy, quite complicated.

Regular expressions are very useful for extracting and replacing information from any text by searching for one or more matches of a specific search pattern. This blog post will not go into great detail about how to use Regular Expressions, but simply how to execute them with Power Automate.

Third party connectors exist which can perform Regular Expressions on behalf of Power Automate, but this blog post will focus on how to execute regular expression actions for free, within Power Automate.

So, as I said – there is no Regular Expression support within the standard Power Automate Actions, but there is regex support built into JavaScript, which can be accessed via Office Scripts in Excel Online. It’s possible to pass parameters into, and out of Office Scripts. So Excel Online can be used as a host for getting access to JavaScript functionality. If you’d like a demo of how this works, check out this video:

Regular Expression Match with Power Automate

Let’s start with an example, If I want to extract all of the email addresses from the string below:

This text contains email addresses, such as [email protected] and [email protected] – also [email protected] has been included and [email protected] is here too.

I can use the following regex pattern, with the flags g and i (global and case insensitive respectivly)

\b[A-Z0-9._%+-][email protected][A-Z0-9.-]+.[A-Z]{2,}\b

I got the regular expression to find email addresses from here. So in Power Automate, this is a simple two step flow:

As you can see from the above, my office script accepts three parameters:

• The string to perform the regex match on.
• The regex pattern to use.
• The flags to pass to the regex object.

and the output from the run script action is:

As you can see, we get back a JSON array of results. This is the code for the regexMatch function:

function main
(
  workbook: ExcelScript.Workbook,
  searchString: string, 
  regexPattern: string, 
  regexFlags: string
) : Array<string> {
  let matches: Array<string> = []
  let re = new RegExp(regexPattern, regexFlags);
  let matchArray: Array<string> = searchString.match(re);
  if (matchArray) {
    for (var i = 0; i < matchArray.length; i++) {
      matches.push(matchArray[i]);
    }
  }
  return matches;
}

Regular Expression Substitute with Power Automate

Another powerful function of regex is the search and replace function. Power Automate already has a useful replace function, but without the ability to find matches first, it can be limited. Let’s try another example:

Hi Customer Service,
Please take payment from my credit card. The number is:
4111 1111 1111 1111. Expiry date is 06/2022 and the CVV is 342.

In this example scenario the string contains a credit card number, for security reasons the objective is to find the credit card number and replace it. Again, this is simple, I used this regex pattern:

\d\d\d\d \d\d\d\d \d\d\d\d \d\d\d\d

with the g flag applied:

This time the function has an additional parameter called replaceString which specifies what matches will be replaced with. The result is:

Note – the above is just an example and will not match all credit card numbers!

Another simple example which would be difficult to achieve with standard Power Automate actions. The following string has too many space characters, and I’d like to replace them with a single space:

I love       Power     Automate, but I     really wish   that it    had support for Regular    Expressions.

Once again, it is very simple with the regular expression pattern \s\s+:

Conclusion

This blog demonstrates that it is possible to add regex support to Power Automate for free, without the use of external connectors. It also demonstrates the additional power that Office Scripts can add to Power Automate by providing access to JavaScript.

But I hope that support for regular expressions is added to the platform, string manipulation is a common requirement of many flows. An idea was submitted on the Power Automate Forum here in June 2019 to add Regular Expression support, but so far no support has been added. So please head over there and vote for it. I’ve also submitted an idea to allow execution of Javascript functions without the need for the Excel connector here, so I’d appreciate your vote on that idea.

The downside to this method is that the “Run Script” action is currently in preview, and it is limited to 200 executions per day. So depending on how frequently your flow runs, it might not be useful.

You can download my two Office Script functions from here:

• Regex Match
• Regex Substitute

Simply place the downloaded files into your OneDrive\Documents\Office Scripts directory and they will become instantly available to you. If you find this useful, I’d be interested to hear your feedback in the comments. What are you using it for?

I’ve written some other useful posts on Office Scripts which you might want to check out:

• Power Automate – How to sort an array
• How to Sum an Array of Numbers in Power Automate