cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jakson
Resolver I
Resolver I

Retrieve email messages - isolate a part of a date string

I've created an MS Form that's basically a questionnaire. Our company collects these from our employees and catalogues them for historical purposes. Basically, I need to retrieve these emailed Forms responses per person, per month.

 

Example:

John Deer's responses.10-29-2021.PDF

 

Any idea how to isolate the month value of the date string? I can determine the person it belongs to by email address.

 

Desired result:

John Deer's responses.10-29-2021.PDF

 

In effect I just need to trim out all the data but the date value. I've done some string manipulation in the past but that's only with VBA.

 

Guidance is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
AgniusBartninka
Advocate III
Advocate III

Use the Parse Text action with some Regular Expressions (RegEx).

Here's a sample code from PAD on how to get what you want, assuming the text is stored in %Text% (you can paste this directly into your PAD):

Text.ParseText.RegexParseForFirstOccurrence Text: Text TextToFind: $'''\\d{2}(?=-\\d{2}-)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match

  You will then end up with the value you want in %Match%:

AgniusBartninka_0-1635667034316.png

 

The idea behind the RegEx above is as follows:

\d - this indicates a digit

{n} - this indicates the quantity of the item before this, meaning that \d{2} indicates 2 digits. You can also use \d\d instead, if you prefer.

(?={something}) is a so-called "positive lookahead". It basically is a part of the pattern that must follow whatever you're looking for, but is not included in the result. So, (?=-\d{2}-) means that your pattern must be followed by a dash (-), two digits and another dash.

 

So basically, the entire pattern \d{2}(?=-\d{2}-) will retrieve any two digits that are followed by a dash, two more digits and another dash.

In all cases of a date in the format of DD-MM-YYYY, you will get the DD part of it.

Also, it will completely ignore everything before the two digits.

 

If you find this helpful, please mark it as the preferred solution.

If you have any further questions to how this works, let me know.

 

View solution in original post

2 REPLIES 2
VJR
Super User
Super User

Hi @Jakson 

 

Here is how the entire flow looks like

VJR_0-1635655585595.png

 

The first Display Message on Line No 2 will get the date part from the text

VJR_1-1635655668046.png

 

The last Display Message will give the name of the month.

If you want the full month name then use MMMM in the formatting.

 

VJR_2-1635655761484.png

 

 

Copy and paste the below code into a new PAD flow and you can make modifications for yourself.

 

Text.SplitWithDelimiter Text: $'''John Deer\'s responses.10-29-2021.PDF''' CustomDelimiter: $'''.''' IsRegEx: False Result=> TextList
Display.ShowMessage Message: TextList[1] Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
Text.ToDateTimeCustomFormat Text: TextList[1] CustomFormat: $'''MM-dd-yyyy''' DateTime=> TextAsDateTime
Display.ShowMessage Message: TextAsDateTime Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
Text.FromCustomDateTime DateTime: TextAsDateTime CustomFormat: $'''MMM''' Result=> FormattedDateTime
Display.ShowMessage Message: FormattedDateTime Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
AgniusBartninka
Advocate III
Advocate III

Use the Parse Text action with some Regular Expressions (RegEx).

Here's a sample code from PAD on how to get what you want, assuming the text is stored in %Text% (you can paste this directly into your PAD):

Text.ParseText.RegexParseForFirstOccurrence Text: Text TextToFind: $'''\\d{2}(?=-\\d{2}-)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match

  You will then end up with the value you want in %Match%:

AgniusBartninka_0-1635667034316.png

 

The idea behind the RegEx above is as follows:

\d - this indicates a digit

{n} - this indicates the quantity of the item before this, meaning that \d{2} indicates 2 digits. You can also use \d\d instead, if you prefer.

(?={something}) is a so-called "positive lookahead". It basically is a part of the pattern that must follow whatever you're looking for, but is not included in the result. So, (?=-\d{2}-) means that your pattern must be followed by a dash (-), two digits and another dash.

 

So basically, the entire pattern \d{2}(?=-\d{2}-) will retrieve any two digits that are followed by a dash, two more digits and another dash.

In all cases of a date in the format of DD-MM-YYYY, you will get the DD part of it.

Also, it will completely ignore everything before the two digits.

 

If you find this helpful, please mark it as the preferred solution.

If you have any further questions to how this works, let me know.

 

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.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

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.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Top Solution Authors
Top Kudoed Authors
Users online (3,710)