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:
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:
Ghita says
Love it! Thanks for sharing!
Inna S says
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.
Michel Mendes says
Awesome!!!
Janice S says
Thank you for this! It’s awesome!!!
Paulie says
Glad you like it! I think it is pretty awesome too!
Tom Wilson says
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.
Paulie says
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.
Max says
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
Max says
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.
Ingo Klug says
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.
Paulie says
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.
Ingo Klug says
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
WONG Jackie Ka Yan says
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
Sviat says
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
Ramon says
this works great, does this also support groups (e.g (?<firstname).*(?) as example ?
Paulie says
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.
Adrián Flores (@AdrianFloresF) says
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?
lbendlin says
This is awesome! Finally a way to do regexp in Power Automate. Then I was thinking – what if I could do the same (run an office script) from Power Query? Turns out that cannot be done (yet?) but it led me down the rabbit hole of running Power Query scripts in Power Automate. I had no idea! Thank you for the inspiration!
Pete Jeavons says
Hi. I’ve been looking at the Substitute regex script which could prove useful to me. However, it appears from following the process for setting it up, that you require a BUSINESS Office account in order to utilise the Excel Online (Business) Run Script? The Run Script connector isn’t available with the non-Business Excel Online (OneDrive) account.
So the use of this excellent regex solution for removing unwanted spaces from text strings can ONLY be used if you have an Office BUSINESS account.
Or am I missing something?
Cheers
Paulie says
If you only want to remove spaces you can easily use a replace expression.
I didn’t even know power automate had a non business version.
Annie says
I’m not getting the 3 parameter fields appear in Automate after *script after selecting the regexMatch.ost in *script.
Any suggestions as to what I might be doing wrong? Thanks
annie says
not to worry – figured it out. I’d saved the download incorrectly