Answered

How to get specific value from email

(0) Share

Report

Posted on by cnazar

Hello!

I have been having a difficult time with this in my flow. I am trying to get a value from an email and place it on an excel row. However, some of these have the same name and is making it difficult for me to use the Split function. The email's format is as follows:

Wallet

Amount in dollars: 777

Amount in Euros: 999

Bank Account

Amount in dollars: 888

Amount in Euros: 555

Total Amount

Amount in dollars: 10101

Amount in Euros: 121212

The number I want is the 888 (the Amount in dollars under bank account). How can I obtain that number with a formula? I used the following formula for the first amount in dollars cause it was the first instance of it and it was easier. But if I use the same formula for the second instance of it, I will get the value from the first one. Basically, how can I differentiate the both? I have been using this for the first instance:

Trim(first(skip(split(first(split(body('Html_to_text'),'Amount in dollars')),'Amount in euros:'),1)))

Any help is appreciated!

I have the same question (0)

All responses (8)

Answers (2)

v-duann-msft on at

Like (0)

Report

Re: How to get specific value from email

Hi @cnazar

Thank you for posting.

According to your description, you would like to extract value from email body and copy to excel. If misunderstood, please kindly let me know.

I followed up your condition and configure email as below:

Here is my expression and flow details:
trim(last(split(first(split(first(split(trim(last(split(outputs('Html_to_text')?['body'],'Bank Account'))),'Total Amount')),'Amount in Euros:')),'Amount in dollars:')))

Once tested, I got correct result:

Hope the content above may help you.

Thanks
Anna

Was this reply helpful? Yes No
cnazar 14 on at

Like (0)

Report

Re: How to get specific value from email

Here's my reply

Hello Anna,

thank you very much for your help. I tried your formula, but is was unsuccessful. Those are only a few lines of the email. When I tried your formula, it gave me the final lines of the email. This would be a sample of the full email:

Wallet
Amount in dollars: 777
Amount in Euros: 999

Bank Account
Amount in dollars: 888
Amount in Euros: 555

Total Amount
Amount in dollars: 10101
Amount in Euros: 121212

Amount in detail:

Wallet in dollars
Amount in dollars JP Morgan (1003): 1727
Amount in dollars BoA (BoA 1101): 5789
Citi Wallet (4647): 0

Wallet in Euros
Fifth third bank: 1685
TD Bank (11800): 2878
Suntrust (1011): 2306

Bank Account in dollars

Pension Fund (1100): 1005
Chase (11001): 7340

Bank Account in Euros
Credit Union (1180): 3911

Regards,
John Smith
XY Bank

When I put in your formula, I got the following into my excel:

in Euros
Credit Union (1180): 3911

Regards,
John Smith
XY Bank

Is there any way that I can isolate that Amount in dollars: 888 in the beginning of the email mentioned in the original post? I have tried different variations of your formula, but sadly, I have yet to get the right answer.

Thank you for your help!

Was this reply helpful? Yes No
cnazar 14 on at

Like (0)

Report

Re: How to get specific value from email

@v-duann-msft

Was this reply helpful? Yes No
Verified answer

Paulie78 8,418 Moderator on at

Like (1)

Report
Re: How to get specific value from email

Try this approach:

Code in first compose:

split(outputs('Html_to_text'), decodeUriComponent('%0A'))

Code on left side of filter array:

length(trim(item()))

Code in final compose:

trim(substring(body('Filter_array')[5], add(indexOf(body('Filter_array')[5], ':'),1)))

Was this reply helpful? Yes No
Verified answer

v-duann-msft on at

Like (1)

Report

Re: How to get specific value from email

Hi @cnazar

Thank you for your further explanation.

I changed the formula based on your email template as below:
trim(first(split(last(split(trim(last(split(trim(first(split(trim(first(split(outputs('Html_to_text')?['body'],'Amount in detail:'))),'Total Amount'))),'Bank Account'))),'Amount in dollars:')),'Amount in Euros:')))

Hope the content may help you.

Thanks
Anna

Was this reply helpful? Yes No
cnazar 14 on at

Like (0)

Report

Re: How to get specific value from email

Hello Paulie,

this worked beautifully! You also solved all my other questions regarding this topic as well. Thank you very much!

I will mark this as solved with your answer later. Thanks again!

Was this reply helpful? Yes No
Paulie78 8,418 Moderator on at

Like (0)

Report

Re: How to get specific value from email

@cnazar No problem, glad it worked for you.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

Was this reply helpful? Yes No
cnazar 14 on at

Like (0)

Report

Re: How to get specific value from email

Hello Anna!

Your formula works! I got the value that I needed. I also broke it down step by step to understand how each function worked by itself and will use that knowledge for further projects. Thank you very much!

Was this reply helpful? Yes No