Parse out number from anywhere in email subject line

[ops_geo]

Hello,

I am trying to extract the ID, which is a number, from anywhere in the subject line of the email. 

I previously posted a similar question where I wanted to know how to parse out just the number from the email when the email format is like this: 

AQQR Application - ABC - ID: 1234

I was able to solve this issue by using the below formula in the Compose action of the flow: 

split(split(triggerOutputs()?['body/subject'],'ID:')[1],' ')[0]

However, now we are receiving emails where people are adding extra additions to the subject line such as: 

1. AQQR Application - ABC - ID: 1234_v2

2. "AQQR Application - ABC - ID: 1234" 

3. ID: 1234- ABC, AQQR app

My question is, how can I parse out the number 1234 from the subject line knowing that sometimes, the email may have additional items before or after the number or can even have the number anywhere in the subject line.

I would really appreciate it if someone could help me figure out this issue. Thank you!

[grantjenkins]

Will the format always be ID: XXXX (where X is a number)? What I mean is, will the ID always contain the text "ID: " before the number?

[ops_geo]

@grantjenkins 
yes xxxxx (where x is a number) will always be after ID:

although, some people have forgot to add the : after ID so we have received subject line like this -> ID 123 for example 

[grantjenkins]

Would definitely need some regular expressions by the looks of it.

Another question - is there a way to get the users to send their emails via a Microsoft Form (or similar) where it would format the Subject Heading of the Email to make it easier to get. For example, on the Form you could ask them to enter the ID and give them an example of what you want (just numbers). Then the flow that picks up the Form Submission would be able to extract that out for you and add it to the email.

Sometimes we over engineer solutions, when in fact, fixing the root cause might be very simple.

[ops_geo]

@grantjenkins 

It has to be an email since they are also sending in attachments as well. Most folks do add the : after the ID, it’s just some one offs forget it. But I am looking to for help to parse out the number from the subject line. The most common subject line scenarios are the ones above  

[grantjenkins]

Ok - I'll see what I can do. I'll likely need to use an Office Script for the Regular Expression, unless you have access to some third-party RexEx connectors?

[ops_geo]

@grantjenkins 

Thank you! I don’t have access to 3rd party RexEx connectors 

[grantjenkins · Accepted solution]

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.

[ops_geo]

@grantjenkins  thank you so much, it took me a while to try and understand this but thanks for introducing me to Office Scripts, looks like I have a lot of learning to do. Quick question - for the regex formula would - [ID: ]([0-9]+)[\w\W]*   - also have worked? 

Also, wondering if you could explain what replace string means and what the $1 means? 

Again, much appreciated for the help!

[VictorIvanidze]

Hi @grantjenkins,

I believe my implementation will be a bit faster:

[ops_geo]

@VictorIvanidze  thank you, the numbers are not always after the word ID, sometimes they are like this - AQQR Application - ABC - 1234
in that case, I need to still grab the number 1234, even though, the word ID is not in front of them. The method you have here is not working for those instances. Is there a way to accommodate of instances where the word ID is missing?

[VictorIvanidze]

@ops_geo, no now only on commercial basis, sorry. Send me a private message if you are ready to pay.