How to use Startswith to pull a value from email if true into excel

[Sigmatus]

Hi everyone,

I'm trying to parse email subjects for a certain string, and then pull the result if there's a match into an excel. It needs to search for the letters EBKG and then pull EBKG and the subsequent 8 numbers into an excel field. The EBKG string can be anywhere in the subject, although it is always followed by 8 digits.

Example Email Subject: Hello, your booking EBKG04281853 has not rolled.
Intended Result: If Startswith "EBKG" (any 8 numerical values) = true, then add EBKG12345678 to row in excel.
If Startswith "EBKG" = false, then ignore and take no action.

Any idea of which expressions would work best for parsing email subject for 4 key letters, and then pulling the key letters and subsequent 8 digits into an excel row?

Thank you so much

[Ryan_B]

Here you go:

First(Split(Last(Split(triggerOutputs()?['body/subject'],'[')),']'))

To make it simpler, I modified an expression I use for something similar. By placing in a separator, you can easily grab all the data between them. There certainly is a way to do so without it, but it gets a bit more complex if you want to go that route. So as long as you don't mind having square brackets, or whatever else you prefer to use, the above code will work for you.

[wskinnermctc · Accepted solution]

Hi @Sigmatus If you are triggering from receiving an email, you can put the subject filter directly into the trigger.

After the trigger you can use the following expression to get the values.

Returns the first part: 'EBKG'
slice({text},indexOf({text},'EBKG'),add(indexOf({text},'EBKG'),4))

slice(triggerOutputs()?['body/subject'],indexOf(triggerOutputs()?['body/subject'],'EBKG'),add(indexOf(triggerOutputs()?['body/subject'],'EBKG'),4))

Returns the last part: '04281853'
slice({text},add(indexOf({text},'EBKG'),4),add(indexOf({text},'EBKG'),12))

slice(triggerOutputs()?['body/subject'],add(indexOf(triggerOutputs()?['body/subject'],'EBKG'),4),add(indexOf(triggerOutputs()?['body/subject'],'EBKG'),12))

Returns the full: 'EBKG04281853'
slice({text},indexOf({text},'EBKG'),add(indexOf({text},'EBKG'),12))

slice(triggerOutputs()?['body/subject'],indexOf(triggerOutputs()?['body/subject'],'EBKG'),add(indexOf(triggerOutputs()?['body/subject'],'EBKG'),12))

These expressions are using the slice() and IndexOf() to separate the text in the subject. You can read about the IndexOf() expression here.

Here is an example flow using the trigger When an Email is Received.

The expression Indexof() is using the text 'EBKG' to find the character position number of the 'E' in the full subject text string.

In the example "Hello, your booking EBKG04281853 has not rolled." The IndexOf() 'EBKG' is 20.

Since there are 12 characters in the 'EBKG04281853' we can add 12 to the 20 to find the character position of the '3' in the full subject text string.

Slice() expression splits a text by character position. So it is slice({subject},20,32) to get 'EBKG04281853' out of the full subject text "Hello, your booking EBKG04281853 has not rolled."

Hope this helps!

[Sigmatus]

Thank you! 

Worked like a charm.

[ManishSolanki]

Hi @Sigmatus 

As per my understanding, you are searching a pattern in the email subject and if found then we need to add the code "EBKG" & subsequent 8 digit number in the excel file.

Here is the flow:

We will check the email subject contains the required pattern using "Condition" action:

In the above screenshot, expressions have been used for comparison purpose which need to be added in expression box as highlighted in the screenshot.

Expression used for left side operand:

contains(triggerOutputs()?['body/subject'],'EBKG')

Expression used for right side operand:

true

If pattern found in the email subject, then we will use filter array action inside "Yes" block of condition to extract the code & 8 digit number:

Expression used for "From" parameter:

split(triggerOutputs()?['body/subject'],' ')

Expression used for left side operand:

startsWith(item(), 'EBKG')

 Expression for right side operand:

true

Next, we will check if any record is returned by filter array action using condition action and if found then we will add a row in the excel file which is present on one drive:

Expression for left side operand:

length(body('Filter_array'))

In "Yes" block, add the action to add row in excel file:

Expression used for saving value in "Code" column in excel sheet:

first(body('Filter_array'))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks