Extracting Specific Text from an Email Body

[BrianRoberts5]

I am currently trying to retrieve anything that comes after 'Company Name: ' up until 'E-Mail Address'. 

I have tried substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Company Name: '), 14),14)

But this ends up giving me 'E-' in the return, and every email trigger is going to have a different length company name.

Here is a sample example of an e-mail trigger the flow will recieve:

---------------------------------------------------------------------------

See communication from Sana contact us form below: 

Reason: Question
Product URL for sample request: Paste product URL here for sample requests

Name: Mike Lester O-Neal

Company name: Test 1025 2
E-mail address: Powerautomate@gmail.com
Phone number: 704194352

Street address: 123 Chicken Lane
City: Austin
State: Texas
Zip code: 00000

Business segment:

Current customer:

How did you hear about us:

Comments:
53463634

---------------------------------------------------

[trice602 · Accepted solution]

Hi @BrianRoberts5 ,

Here's how I would solve this solution.  

I use a few compose actions including indexof and substring, similar to what you are already doing.  Let's review mine:

My first compose is:

add(indexOf(body('Html_to_text'),'Company Name:'),14)

What I am doing here is finding the starting position of "Company Name:" and adding 14 characters to that position with add() which adds two numbers together.  This gives me the starting position of the dynamic company name.

My 2nd compose is:

indexOf(body('Html_to_text'), 'E-mail address:')

This gives me the starting position of the phrase "E-mail address:"  With the two compose actions, I now have the starting and ending character positions to use substring in my last compose.

My 3rd compose is:

substring(body('Html_to_text'),outputs('Compose'),sub(outputs('Compose_2'),outputs('Compose')))

Here, I want the text between "Company name:" and "E-mail address:".  I do that with substring().  The first parameter is easy, it's the starting position, so the output of the 1st compose.  Easy enough.  My last parameter is the length I want to capture and this is dynamic so I use a sub() to subtract the starting position of "E-mail address:" - the starting position of the "Company name:" (remember, plus 14 characters which is the length of the phrase and a space".

This will give you the company name and is dynamic at the starting and ending positions.

[trice602 · Accepted solution]

Hi @BrianRoberts5 ,

Here's the output from the compose actions so you can see the character positions and can help you see the values to associate with the final compose.

[BrianRoberts5]

This worked flawlessly. Thank you for the breakdown at each compose as well - moving forward I now have a way better understanding of how to approach this rather than trying to do it all in one compose statement.

[trice602]

Hi @BrianRoberts5 ,

Glad this helped!  It's also the free route without a premium connector (although there are some good ones out there to consider too).  This works well if you need to capture 10-15 fields in an email, granted it does create a lot of compose actions and can get messy but as you said, this does work well. 😀

[BrianRoberts5]

Hello,

I've run into a problem with the extracted data. It appears to have an invisible '\n' behind it when looking up into business central, here is the flow for reference:

[trice602]

Hi @BrianRoberts5 ,

You can also add an additional 4th compose to replace that character.

replace(outputs('Compose_3'), decodeUriComponent('%0D%0A'), '')

If this one works too, please mark as a solution as well (it's an FAQ).

[trice602 · Accepted solution]

Hi @BrianRoberts5 ,

If I can recommend a better option though.  It appears my original flow is grabbing an extra character which is that invisible newline return.  Here's how to remove it completely:

add(indexOf(body('Html_to_text'), 'E-mail address:'), -1)

Here I adjust by compose 2 by one character:

With this, you may not need the 4th Compose because it is no longer going to capture that character position.

[AlexEncodian]

@BrianRoberts5 

If you need a quick and maintenance free solution, there's also Encodian's Utility - Extract Text between Values action.

It also has a trim string option to remove an spaces or line breaks at the end of beginning of the result.

FYI – actions with the “Utility“ prefix only consume 1/20th of an Encodian action count . For example, a Standard plan has 500 actions so utility actions can be used 500x20 = 10,000 times. They are designed to be an incredibly cost effective productivity boost for your flow builds and there are dozens of them available.

[Safriguy]

Hi @trice602,

I have a similar scenario  to @BrianRoberts5 however I need to extract values for a number of items, not just Company Name as in this example. Is there an efficient way to do this? There could be anywhere from 6 to 15 items in an email I need to extract values for. 

Cheers,

[NTerrio]

Thank you soooooo much for posting this!!!  Wish I had happened upon this months ago! ❤️

[TomPeacock130]

Hi,

 

I have used this, and sometimes it works well. But sometimes it breaks when the indexed text is on different lines as below:

 

Phone

number: 

 

The index does not pick it up.

 

Is there anyway to fix this? 

 

Thank you!