HI there
I have built a flow interrogating an excel sheet / table.
From a cell containing a large report which is text containing letters, symbols and numbers, I am able to extract data. Each question answer is placed on a row in the spreadsheet. The largest block of text is in one column along the row.
I am able to use indexof and substring to extract answers. There is a template that is for the report so the answers should be between known phrases, words and sentences which are part of the template.
For example
The report states
TECHNIQUE: PROSPECTIVE
DOSE: HIGH
So I can use indexof to find where "TECHNIQUE:-" ends and where the word "DOSE" starts and take a substring of the bit in between (in this case "PROSPECTIVE") which is what I need to ultimately update a sharepoint list.
However, users sometimes do not give single number answers.
One of the report headings is
"HEART RATE: bpm". Users should enter ONE heart rate value but sometimes put "52-55"
(e.g. HEART RATE: 52-55 bpm)
I want one number only.
I dont want to have to go through the sheet of 150 or so entries changing the HEART rate values to one or the other or average.
Is there a way to get powerautomate to find the xx-yy bpm phrase and then get it to pick either xx or yy to get one number out?
I could do it in excel first, extract the data using FIND and MID but that takes time.
Heshan
