Ok, I've got it working using an Office Script (replace regex function).
This will work in all of the scenarios. I'm assuming the actual ID will be a sequence of NUMBERS only.
- ID1234
- ID:1234
- ID 1234
- ID: 1234
- Id: 1234
- id 1234
- ID : 1234
- ID 1234
- Etc.
Firstly, you'd need to create a new Office Script so you can use RegEx in your Power Automate (assuming you don't have a third-party RegEx connector).
It's important to note that when you create an Office Script it's stored in your One Drive, and if you want others to use it you will need to share it with them. There is now an option to save your Office Scripts in a SharePoint Library, but unfortunately (at this stage) you can't then use them within Power Automate.
To create a new Office Script, create/open an Excel File in the Browser and click on Automate on the Toolbar, then click on New Script.
In the Script code editor that appears, Rename the script to something like regexReplace, then paste in the following which is a script that uses Regex.
function main(
workbook: ExcelScript.Workbook,
searchString: string,
regexPattern: string,
regexFlags: string,
replaceString?: string): string {
if (typeof (replaceString) === 'undefined') {
replaceString = '';
}
let re = new RegExp(regexPattern, regexFlags)
return searchString.replace(re, replaceString)
}
You can now use that script in any of your Power Automates.
For your requirement, see the flow below. I'll go into each of the actions.
When a new email arrives is just looking for emails that contain the word AQQR (for this example).
Run script is an Excel Online action that you can use. It doesn't matter what Excel file you choose here as the Office Script itself lives in your OneDrive.
Script will be the script that you just created. It should show up as an option to select.
searchString is the text you want to check. In this case it's the Subject from the email received.
regexPattern is the pattern we want to match.
regexFlags has i so it's not case sensitive.
replaceString specifies what you want to replace the matching string with.
Our regex pattern in this example is below. It is effectively checking for the letters ID followed by zero or more colons or spaces, then any number of digits. The [0-9]* is also wrapped within braces () to make it a group, so that we can extract that part out. So, in our case, it will end up matching the entire Subject and replace it with just the numbers - our ID.
[\w\W]*ID[: ]*([0-9]*)[\w\W]*
replaceString is saying replace the match (the entire string) with just the result in our first ($1) group (which will be our ID).
Ultimately, the result of our script will return just the ID.
