Extract all file numbers from an email subject line and insert each into a multi line text sharepoint column

[nana001]

I have a flow that triggers upon receipt of an email. I would like to extract multiple file numbers from the subject line of the email, and insert each of those file numbers into a separate line of a multi line text sharepoint online column

For example, the email subject line: 

Hello world [File:123][File:234]

I would like to have the following strings extracted and each inserted on a separate line of a multi line text sharepoint online column:

123

234

Can someone kindly help? Thanks

[abm]

Hi @nana001 

Use a split expression

Then extract the index from 1 and 2. Please see below.

The above expressions I used is as follows:

split(replace(outputs('Compose'),']',''),'[File:')

outputs('Compose_2')[1]

outputs('Compose_2')[2]

My run result:

[efialttes]

Hi!

My suggestion is:

1.- Initialize variable, type array, name 'myFileIDArray', assign as value the following expression:

split(join(skip(split(triggerBody()?['Subject'],'[File:'),1),''),']')

2.-Initialize variable, type array, name 'myFileIDArray2', assign as value the following expression:

take(variables('myFileIDArray'),sub(length(variables('myFileIDArray')),1))

3.- Add Compose action block, assign as value the following expression

join(variables('myFileIDArray2'),'
')

4.- Execute the flow, inspect values assigned both to the variable and to the Compose action block to make sure all File IDs were identified properly

Now you should have your input ready to upload it to a multi line text sharepoint online column

Hope this helps

[vikulkar]

Hi @nana001 ,

Does your subject line of the email vary everytime or is there a fixed pattern?

If yes: you should be able to use the trim/substring/length/indexOf expression to extract the file name as desired.

Here is a list of string function that you could use:

concat	Combine two or more strings, and return the combined string.
endsWith	Check whether a string ends with the specified substring.
formatNumber	Return a number as a string based on the specified format
guid	Generate a globally unique identifier (GUID) as a string.
indexOf	Return the starting position for a substring.
lastIndexOf	Return the starting position for the last occurrence of a substring.
replace	Replace a substring with the specified string, and return the updated string.
split	Return an array that contains substrings, separated by commas, from a larger string based on a specified delimiter character in the original string.
startsWith	Check whether a string starts with a specific substring.
substring	Return characters from a string, starting from the specified position.
toLower	Return a string in lowercase format.
toUpper	Return a string in uppercase format.
trim	Remove leading and trailing whitespace from a string, and return the updated string.

[nana001]

Thank you everyone for your responses

I tried this solution from efialttes, which works unless there is text after the last file number

For example:

Hello world [File:123][File:234] This email is [important] but I do not want all of it

The result is:

123
234
 This email is [important

However, I am looking for this result:

123

234

Please help 🙂

[abm]

Hi @nana001 

Did you tried my solution?

Thanks

[nana001]

abm: Thank you for your proposed solution, however the problem I see (and why I proceeded with the other proposed solution) is that your solution will only work if there are always 2 file numbers present. If there are more than 2 file numbers, it will not pickup the 3 or subsequent file numbers. In my situation, there are an unknown number of file numbers that will be present. 

The problem that I am encountering with the other solution is that there may be other "]" present in the subject, as it is a common character for routing emails in my industry. So the current solution picks up all of those other unwanted routing (file) numbers, even though they don't have "[File:" in front of them - as the string is also split by "]". Is there a way to modify the solution so that it only returns the string before "]" if it has "[File:" in front of it?

[abm]

Hi @nana001 

Thanks for your quick reply. I was showing how to get the values using index values as a reference. You can pass the split output to a for each loop so that it will get each item whether its 1, 2, 3 or etc.

Thanks

[efialttes]

@nana001 

If I were in a Scape Room I would ask my money back 😋

Both @abm and me provided valid solutions according to your initial problem description hahaha.

So you are a lucky man, this community is SO great that offers the possibility for you to redeem your soul 😇, no matter none of our current suggestions is finally acepted as a valid solution, you can just click on on 'Thumbs up' in every answer you found valuable in this current thread. And remember some great members of this community tried to help so far

Now let's talk about the new requirements. Please note something that would be solved in 5 minutes with regex, can take days to be solved with WDL, lol

1.- Does the nr of digits inside your [File: xyz] remain the same -3-?

2.- Does your users insert manually more than one [File: xyz] on the emails Subject or is this insertion is somehow automated?, and

3.- If the answer to question#2 is: Automated.... Are you also the one behind the automation and can introduce changes? That is for example to use [File:124:File] instead. If so, my first suggestion can be adapted easily to match your new requirements:

split(join(skip(split(triggerBody()?['Subject'],'[File:'),1),''),':File]')

If not, just write back since I got a more complex alternative already in my mind (and it looks like one of our three top community members, @abm , has another one also 😁)

Thanx!

[nana001]

Thank you for your replies. This is my first post here, sorry for the confusion in both the scope of the issue requiring a Flow solution and my replies. As always, issues are discovered when running some test emails through the flow. I also did not see the thumbs up button, thanks for pointing it out 🙂

With respect to your three questions:

1. The number of digits for a file number is variable, and may include numbers, letters, or even special characters ( ) - . or similar, but not a '[' or ']'

2. Users manually insert the file numbers into the subject of emails, although I will be looking for a solution in the near future to automate the insertion of file numbers by reference to an existing SharePoint online list that contains the file numbers and descriptions thereof. If you have a link for such a solution for outlook, that would be much appreciated 🙂 Also, there is a concern that persons outside our office may change the email subject line on an email chain which will then be read by the Flow coming back

3. Insertion of the file numbers is not automated at this time

[efialttes]

Hi!

"Users manually insert the file numbers into the subject of emails"

I run a similar schema some months ago, just to realize more than 5% of the emails received had pattern typos.

What do I mean with a patter typo? Something that is not recognized by the trigger, so my Flow is not executed

My favourite one was to realize someone added + instead of ]

So I had to rebuild my expressions several times, in order to avoid most frequent errors: remove spaces and convert to capital letters just two of them... I currently managed to reduce typos under 2%

Not currently on my laptop, as soon as I get back I will work on a new approach for your [File:123] challenge.

Thanx for giving more precise details from your challenge!

[efialttes · Accepted solution]

Hi again

Let's see if this new approach fits better:

1.- Initialize variable, type array, name 'myFileIDArray1', assign as value the following expression:

skip(split(replace(triggerBody()?['Subject'],']',''),'[File:'),1)

2.- Initialize variable, type int, name NrOfFiles, assign as value the following expression:

length(variables('myFileIDArray1'))

3.- Initialize variable, type array, name 'myFileIDArray2', assign as value the following expression:

take(variables('myFileIDArray1'),sub(variables('NrOfFiles'),1))

4.- Append to array, input name 'myFileIDArray2', assign as value the following expression

first(split(last(variables('myFileIDArray1')),']'))

first(split(last(skip(split(triggerBody()?['Subject'],'[File:'),1)),']'))

...and I think you got it

Time for dinner with my family, be back in a while to test it

Hope this helps

[efialttes]

@nana001 

My design from last night had a couple of errors, I just fix them, tested with two examples:

Hello world [File:123][File:234] This email is [important]

Hello world [File:123][File:234]

and working fine!

I've just updated my last post to detail the new expressions

Hope this helps

[nana001]

Thank you kindly efialttes, that works, I will continue to test with real data to make sure it continues to work 🙂

Just a note to people who may read and want to use the solution, you need to put a condition prior to the expression for myFileIDArray2 to check if NrOfFiles is greater than 0, if it is not you will get an error. Ie, if there are no file numbers in the subject line of the email

[umairrehman]

How to extract the number from the Body of email

i will be receiving email with an incident  as below

"

Dear Customer,

Kindly informed that we are working on your request with the incident # 200412-000033

"

i want  that Incident Number to be extracted and a row to be created in excel with date and time email to be sent to IT 

[efialttes]

---

@umairrehman wrote:

How to extract the number from the Body of email

i will be receiving email with an incident  as below

"

Dear Customer,

Kindly informed that we are working on your request with the incident # 200412-000033

"

i want  that Incident Number to be extracted and a row to be created in excel with date and time email to be sent to IT 

---

Hi!

My suggestion is

---

replace(last(split(triggerBody()?['Body'],'#')),' ','')

If you need further assistance I would suggest to open an independent thread, this way you can grab the attention from more members of the community willing to help

Hope this helps