cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User
Dual Super User

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

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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

15 REPLIES 15
Highlighted
Super User
Super User

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

Hi @nana001 

 

Use a split expression

 
Then extract the index from 1 and 2. Please see below.
 
 
image.png
 
The above expressions I used is as follows:
 
split(replace(outputs('Compose'),']',''),'[File:')
 
outputs('Compose_2')[1]
 
outputs('Compose_2')[2]
 
My run result:
image.png
 


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Dual Super User
Dual Super User

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

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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Community Support
Community Support

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

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.
Highlighted
Advocate I
Advocate I

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

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 🙂

Highlighted
Super User
Super User

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

Hi @nana001 

 

Did you tried my solution?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Advocate I
Advocate I

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

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?

Highlighted
Super User
Super User

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

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Dual Super User
Dual Super User

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

@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

 

Flow_thumbsUp.png

 

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Advocate I
Advocate I

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

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

Highlighted
Dual Super User
Dual Super User

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

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!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User
Dual Super User

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

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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Dual Super User
Dual Super User

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

@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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Advocate I
Advocate I

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

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

Highlighted
Regular Visitor

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

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 

Highlighted
Dual Super User
Dual Super User

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


@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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (6,210)