Hi
@gouravvishnoi
This is achievable via the Text functions available in Blue Prism. I've just experimented and I've used the InStr() function to find the position of each colon in the text and then scrape everything between the colon and the next expected word (Technology or Issued). This will work as long as the format of the text stays the same. Here's what I came up with:
I start by launching the excel file and setting A1 & B1 to be the headers you mentioned - Technology and Issued. Then I begin to split out the text:
I use Colon Pos to find where the first colon exists. Then Issued Pos finds where the word "Issued" exists. I can then scrape everything between those two positions to read back the technology value. Scrape is as follows:
Trim(Mid([Text], [Colon Position]+1, ([Issued Position]-1)-[Colon Position]))
I then delete the first line of the text like so:
Right([Text], Len([Text])-([Issued Position]-1))
I'm now ready to read back the Issued Date by scraping everything between the end of the word "Issued" and the position of NewLine(). We can then repeat these steps to go back and read the rest of the values you require. You could choose to write these to the Excel file one by one or you could store them in a collection and write the collection once you're finished.
I hope this helps. As mentioned above, this process relies on the text staying in the format of:
Technology : x
Issued : y
Technology: z
Were the format to change, then the formulae would need to be changed accordingly. Please note, I realise the above process isn't complete - I was looking to explain the theory mainly.
Rob