Extract information such as amount, date, etc

[yvkpatel]

My desired output:

The Principal sum	$21760000.00
First Installment Due Date	1/11/2018
First Principal and Interest Installment Due Date	1/11/2021
Maturity Date	1/10/2025

I used Regex tool and got the outputs in Field_11 after that I want to form 2 columns as shown above. How can I find November1,2018 using some tool in the Field_11 and convert it into the format DD/MM/YYYY?

I also want to remove all the irrelevant rows.

I have attached my workflow

[LordNeilLord]

Hey @yvkpatel 

Can you include the sample text file?

Without the text file to hand you could try something like: .*means(.*?) to get the month and date, then DateTimeParse(New Field, "%B%d,%Y")  to format the date

[yvkpatel]

PFA - Sample Text file

[estherb47 · Accepted solution]

Hi @yvkpatel 

Took a slightly different approach, as I couldn't reconcile the RegEx tool to parse both types of columns you needed.

So, here goes. A Filter uses Regex_Match to isolate just the rows you want. There are duplicates that will be removed later with the Unique tool.

The Formula tool does the separating. For the "Principal sum" heading, that's a simple if the RegeX Match function from the filter tool is true, then use the "The Principal Sum". Otherwise, grab the text that's in between the quotation marks only.

For the Value column, again, using the Regex match function to determine number versus date. For the date, it's a bit convoluted. Look for the string pattern within the field of a word that begins with a capital letter and ends in "er", followed by a space, a number, a comma, another space, and a 4 digit number. Use DateTimeParse to convert that into a date, and DateTimeFormat to convert it back to a string.

Let me know if this helps!

Cheers,

Esther

[yvkpatel]

Thanks @EstherB47 for your solution. It was very accurate and clean approach.