How to use Regular Expressions in Power Automate

If you want to search for a specific pattern in a text, the best choice is to use regular expressions. You can find ready-to-use patterns and quickly implement them in your case. But what to do if you want to use it in Power Automate? You don’t need to use 3^rd party tools or a premium license – there is one action that you can leverage to use RegEx in your Flows.

Last week I enhanced a customer’s flow with advanced string analysis (check if a bank account is properly formatted, etc.). The ideal solution is regular expressions, but Power Automate has only basic string functions (for example, replace, substring). I could use some 3^rd party actions (for example, Plumsail) or a custom Azure function – both works fine but require additional licenses/costs. Is there any other way to do this? Yes – Excel Run Script action!

This action is a standard one (does not require a premium license). The action runs an Office Script against an Excel workbook. But what is that script? Office Scripts lets you automate tasks in Excel on the web. You can record actions in Excel and save them as a script. Excel saves it in TypeScript language. You can also create it from scratch, and we will use this technique to create a script for regular expressions.

Creating Office Script

1. Open Excel on the web, create a new blank workbook, and navigate to Automate section.

2. Click New Script

3. Replace a default script with the following script and save the script (I saved it with regex name).

Script:

function main
  (
    workbook: ExcelScript.Workbook, inputString: string, pattern: string, flags: string
  ): Array < string > {
    let regex = new RegExp(pattern, flags);
    let matches: Array < string > = inputString.match(regex);

    if(matches) {
      return matches;
    } else {
      return [];
    }
  }

Use script in Power Automate

Add the Run script action to your Flow (in this case I used a text file with an account number in IBAN format along with lorem ipsum text).

The action adds more fields for parameters available in Office Script. In this case:

• inputString – text in which you want to search for a pattern (in this case text file content from a SharePoint site)
• pattern – regular expression pattern (in this case search for polish IBAN bank account number)
• flags – parameters for regular expressions (in this case global and multiline)

Results – the Flow found one IBAN account number that matched the provided pattern.

Conclusions

Using the Run script action, you can quickly add regular expressions to your Flows without any premium licenses or 3^rd party actions/connectors. It requires only an Excel file with the provided script and basic knowledge of regular expressions. You can change the script or create your own with more advanced features (for example, count occurrences, replace matched text).

You can also use this approach in Logic Apps – but that’s a different story with more options and things to consider.

Pros:

• Easy to configure
• No premium license needed
• Customizable

Cons:

• Required an Excel file
• Can’t be used as an expression
• Can be slow

Resources

Additional resources:

• Office Scripts in Excel – Office Scripts in Excel – Office Scripts | Microsoft Docs
• Regular Expressions online builder tool with examples, references, and flag descriptions – regex101: build, test, and debug regex