cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YMN
Frequent Visitor

HTML to Text - Parsing variable length data

Hi All,

I have been trying to build a flow that collects the data from an e-mail and writes it into an excel using add a row into a table function. 

Other fields are working just fine however, I need to split the last line into the two fields as follows:

 

Field1: xxxx

Field2: xxxx

Field3: xxxx

Amount to Pay: 4,344,772.60 in USD

 

YMN_1-1624954584917.png

I was using the expressions below for big amounts, but the problem is that amount is changing between <100 and 10 of millions. 

It was working just fine until I receive small amounts. When amounts are too low like 66.70 in USD columns are shifting as expected and I see something like '66,70 in U' in Amount to Pay column and 'SD' in Currency column. 

 

Amount to Pay: 
substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'), 14),12)
 
Currency:
substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'), 31),3)
 
 

How can I adjust my flow so that it can properly parse the information that I need? 

Many thanks.

 

 

 

 

 



1 ACCEPTED SOLUTION

Accepted Solutions
VictorIvanidze
Community Champion
Community Champion

Use these expressions:

 

Amount to Pay: 

split(substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'),14)),' in ')[0]

 

Currency:

split(substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'),14)),' in ')[1]

 

 

--------------------------------------------------------------------------------------
Contact me if you are interested in custom Power Automate development.

View solution in original post

7 REPLIES 7
PrasadAthalye
Super User
Super User

Use Split function

 

For Amount to Pay use below expression

split(split('Amount to Pay: 4,344,772.60 in USD', ':')[1], 'in')[0]

For Currency, use following

split(split('Amount to Pay: 4,344,772.60 in USD', ':')[1], 'in')[1]

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

Hi Prasad,

Thanks for your comment, but amount is changing every time.

For example, next time it may be ' 54.50 ' or ' 19,888,225.20'.

 

I need a solution that works regardless of how big or small the amount is. 

Please implement the solution and test. It will work regardless. We are splitting on 'in' word. So it does not matter how big is amount. Text will be splitted into 2. One before in word and one after.

 

It is not using Substring rather its using split.

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

Hi Prasad,

Many thanks for taking the time solve my issue, it's much appreciated.

I implemented your code but unfortunately it didn't work. 

Amount and Currency remained constant, no matter what I put in the e-mail.

 

I changed the amount to random numbers twice and changed the currency once to EUR, but still flow added same amount and same currency. 

 

YMN_0-1625036015588.png

 

VictorIvanidze
Community Champion
Community Champion

Use these expressions:

 

Amount to Pay: 

split(substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'),14)),' in ')[0]

 

Currency:

split(substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'),14)),' in ')[1]

 

 

--------------------------------------------------------------------------------------
Contact me if you are interested in custom Power Automate development.
YMN
Frequent Visitor

many thanks for your support! 

I am curious to know what you implemented... I gave you example considering a particular text. I guess you should have replaced actual hardcoded text to your expression body('Html_to_text'). Anyways, I am glad issue is resolved...

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (4,156)