Parse an email subject line to get certain pieces to fill in a SharePoint list

[ktownshend]

Hi,

Building a flow that will take an email from a shared mailbox, get the subject line, take pieces of the subject line add it to a SharePoint list and any attachments or body of the email if there's no attachments.

Where I'm currently getting hung up on is the parsing out of the subject line.

Sample: Confirmation for Customer 0/10478 Costco IL / MW Order # 1079170

I need to be able to pull out the 0 and then the 10478 and then the 1079170 and then Costco IL/MW.

I'm pretty sure I need to build an array to get all this and then use it to fill in the columns in the SharePoint list, but I'm having a hell of a time with it.

I think I need to use Split to get what I'm looking for and I think I need to build 4 of them, but I'm not sure where to start, tried searching the web for help but nothing is matching what I need, or I just don't understand it.

Any help or suggestions of things to look at would be greatly appreciated.

Thank you. 

Kendra

[rzaneti · Accepted solution]

Hi,

I'm assuming that your subject will always follow the same name convention [data1]/[data2] [data3] Order # [data4].

If this is the case, you can use the following expressions:

Main text

For building this example, I stored your sample in a variable called 'text':

Using expressions

You will need some expressions to handle your text. Please, follow these instructions for all of the specific data extraction steps below.

1. Click anywhere in the input box from the action where you want to insert the data (highlighted in yellow): it will shows a 'dynamic content' box.

2. Change to the 'Expression' tab (highlighted in green).

3. Paste your expression in the input box (highlighted in blue).

Expressions

Use the following expressions to extract each specific part from your text. Make sure to change the variables('text') for the dynamic content of your email subject 

Zero: 

Expression: trim(first(split(variables('text'), '/')))

Output: 

10478

Expression: trim(first(split(split(variables('text'), '/')[1], ' ')))

Output:

1079170

Expression: trim(last(split(variables('text'), '#')))

Output:

Cotsco

Expresssion: trim(first(split(last(split(variables('text'), variables('10478'))), 'Order')))

Output:

Note1: For the 'cotsco' variable, we are using the output from 10478 (bold and green) as input to one of our 'split' actions.

Note2: As you are starting in Power Automate, I recommend you to store these expressions in variables (as I did) and then use these variables to populate your other actions, where the data actually needs to be inserted. It is useful in case of your name convention changes in the future and to find any errors.

Quick explanation on the used expressions:

• Split: divide a text (string) into array elements, based on a separator. In our case, we used blank spaces, '#', '/' and even another variable as separator, depending on what we needed to isolate;
• First: access the first element from an array;
• [1]: access the 2nd element from an array (the arrays in Power Automate are 'zero-indexed', so they counting starts in zero);
• Last: access the last element from an array;
• Trim: remove any blank spaces from the beginning or the end of a text. It is useful to clean your data before saving somewhere else. 

If you need any additional clarification, feel free to ask.

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

[ktownshend]

Thank you so much for your help, I'm working through this now.  

One question, you state, "I need to change the variables('text') for the dynamic content of your email subject."

I'm not sure what you are asking me to do here. 

Every email that comes into this shared mailbox will have a different set of data from data 1 to data 4 as you outlined above. Will this capture that seeing I've defined the initial variable with the example data?

Sorry, very new to this stuff, I do have limited programming skills so this sorta makes sense to me, but just not sure how Power Automate likes things done.

Thank you

[ktownshend]

Thank you so very much, I figured out what was needed and got this working perfectly.

Kendra