HTML to Text - Parsing variable length data

[YMN]

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

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.

[PrasadAthalye]

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]

[YMN]

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. 

[PrasadAthalye]

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.

[YMN]

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. 

[VictorIvanidze · Accepted solution]

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]

[YMN]

many thanks for your support! 

[PrasadAthalye]

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...