• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

How to use Regex in Power Automate

April 17, 2021 by Paulie 17 Comments

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:

Image of a Power Automate Flow performing a regular expression (regex) match to find email addresses in a string.

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:

Image showing results of a regex match in Power Automate.

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:

Example of a regex in Power Automate to find sensitive information in a string.

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

Result of Power Automate Regex action replacing sensitive information in a string.

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+:

Example Power Automate flow using regex (Regular Expressions) to remove space characters from a string.

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

Filed Under: Power Platform Tagged With: Power Automate

Reader Interactions

Comments

  1. Ghita says

    April 18, 2021 at 8:07 am

    Love it! Thanks for sharing!

  2. Inna S says

    April 19, 2021 at 7:57 am

    Very nice indeed.
    Depending on the tools you feel comfortable with, Azure Function with a simple JS program calling a single match function may be even easier. Added bonus: no limit on daily executions, 1 mln runs / month free.

  3. Michel Mendes says

    April 20, 2021 at 10:01 am

    Awesome!!!

  4. Janice S says

    April 20, 2021 at 10:51 pm

    Thank you for this! It’s awesome!!!

  5. Paulie says

    April 20, 2021 at 11:04 pm

    Glad you like it! I think it is pretty awesome too!

  6. Tom Wilson says

    April 22, 2021 at 2:17 am

    Thank you – great video post! Do you know if there is a way to do RegEx grouping with the Office Scripts language (TextScript?)? I have found some references for C# but nothing for this language.

  7. Paulie says

    April 22, 2021 at 9:39 am

    The Office Scripts language, is called TypeScript, which is a superset of JavaScript. So the answer to your question is that it is perfectly possible. If it can be done in JavaScript (which it can be) it can be done in Office Scripts. Instead of searching for TypeScript examples, look for JavaScript ones instead.

  8. Max says

    May 19, 2021 at 1:24 pm

    Really nice. Thanks for that.
    I just saw that there is another idea in the power users community that is under review:
    https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Support-for-regex-either-in-conditions-or-as-an-action-with/idi-p/24768

  9. Max says

    June 14, 2021 at 6:52 pm

    Damn. I love it! Thanks!
    Do you know also a possibility to create a hash (sha256) value?
    Would be nice to have this possibility in Power Automate or at least in Office scripts.

  10. Ingo Klug says

    July 6, 2021 at 2:36 pm

    Call me stupid, but how do I load the “RegexMatch.osts” into my Excel-File?
    Google gives no answer. Maybe it is far too easy, or I am just blind. The osts looks like JSON and opens directly into my browser. I can put it on SharePoint, but I can not integrate it in Excel. Sorry for misspelling, I am German.

  11. Paulie says

    July 6, 2021 at 2:38 pm

    As per the blog post instructions:

    Simply place the downloaded files into your OneDrive\Documents\Office Scripts directory and they will become instantly available to you.

  12. Ingo Klug says

    July 6, 2021 at 2:42 pm

    Ok. First I have to use one-Drive, bevor upload to Sharepoint. Please delete my comments as they are useless for the community and shame on me. ;P

  13. WONG Jackie Ka Yan says

    July 9, 2021 at 9:42 am

    Hi Paul, your script is amazing. but I hint a problem when I input the search string from Outlook email body, and there is a line breaks in the content. example

    Caller: Mr A
    Category: typeB
    Subcategory: typeBC

    when I apply the following Regex pattern
    (?<=Caller: ).*(?=Category: )

    It output the result below:-
    Mr A
    Category: typeB
    Sub

    My target output should be:-

    Mr A

    It seem something bypass the line break and search to the next pointer to result the output. Would it relate to Office script regex has unquire syntax format?

    Regards,

    Jackie

  14. Sviat says

    August 26, 2021 at 10:53 am

    Hi Paul,

    Thank you for your article.

    I follow 1 example and spot that you’re showing slight different regex pattern on YouTube and here is difference is one character, but impacting regex output in video you used – \b[A-Z0-9._%+-][email protected][A-Z0-9.-]+\.[A-Z]{2,}\b – on that page you’re missing one backslash \b[A-Z0-9._%+-][email protected][A-Z0-9.-]+here.[A-Z]{2,}\b

  15. Ramon says

    October 25, 2021 at 6:36 pm

    this works great, does this also support groups (e.g (?<firstname).*(?) as example ?

  16. Paulie says

    October 25, 2021 at 6:37 pm

    Hmmm, I guess it would but never tried it. JavaScript certainly does so you could easily modify it to support groups if it didn’t already work.

  17. Adrián Flores (@AdrianFloresF) says

    January 7, 2022 at 6:15 pm

    HI, there!

    Thanks for this. I used this method to find patterns in an email, but I’m having problems with the script taking sometimes several minutes to run, even though it’s a rather simple match. Most of the times it takes a few seconds and I don’t understand why it sometimes takes so long.

    Have you had this experience with running scripts in Excel?

Leave a Reply Cancel reply

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee
Image link to all Power Automate content

Excellent Power Automate Blogs

  • Damien Bird
  • Dennis (Expiscornovus)
  • Tom Riha

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 245 other subscribers.

Go to mobile version