Start a new topic
Answered

RegEx grad date mm/dd/yyyy to Class of: yyyy

I'd like to extract the class year (yyyy) from the graduation date(mm/dd/yyyy). Is this possible with a regular expression? New to this and not sure where to start.

Best Answer

Hi Bob!


If your original data is in this format, XX/XX/XXXX, do this:

For the Replacement Value type in:

$3

Press the “+” sign to add it to the list. Then highlight the “$3” that you added. Then type in this for the Value to match on:

(^\d+)/((?<=/)\d+(?=/))/(\d{4}$)

 

If your original data is in this format, XX/XX/XX, do this:

For the Replacement Value type in:

$3

Press the “+” sign to add it to the list. Then highlight the “$3” that you added. Then type in this for the Value to match on:

(^\d+)/((?<=/)\d+(?=/))/(\d{2}$)

 

If your original data has a time stamp, do the following:

For the Replacement Value type in:

$3

Press the “+” sign to add it to the list. Then highlight the “$3” that you added.

Then type in this for the Value to match on:

(\d{1,2})/(\d{1,2})/(\d{2,4}).*


These options will each pull out the 4 digit year from the original string. Make sure you select the “Use Regular Expression box” at the bottom.



1 person has this question

Hi Therese,

Yes, you can use a regular expression to extract the year. Check out #17 on this list in our Knowledgebase.

Enjoy!

Amanda
Omatic Software

Therese, here is an alternate one you can copy/paste if you haven't already gotten your solution from the knowledgebase article: COFROMGRADDATE.xls

Keep up the good work! -Allison B.

image

Does something need added to the code if the date is brought in with a time stamp?

Thanks,
Sara
So now my dates are coming from the other system with a time stamp: i.e., 12/1/2015 12:00 AM
I tried reformatting and cannot remove it.
I still need to extract the class year (yyyy) from this format: MM/DD/YYYY 12:00 AM
Is it possible to modify this: ^.*?(?=\d{4}$)
Class of year can still be obtained from a date, even with a time stamp present. This RegEx dictionary will have 2 entries instead of 1, feel free to copy and paste from excel into a new dictionary here: Class Of from Date with Timestamp.xlsx

 

Both of these entries will go into a single dictionary, order matters!

First you will strip the time stamp, then pull the year for class of:

*Replacement Value 1: -BLANK-

*Value to Match on 1: (?

 

**Replacement Value 2: $3

**Value to Match on 2: (\d{1,2})/(\d{1,2})/(\d{2,4})

 

Keep up the good work!

 

Allison

 

Thank you! That worked perfectly!
You could skip the first step in this by just adding .* to the end of the second matching value

match
(\d{1,2})/(\d{1,2})/(\d{2,4}).*

replacement
$3

The .* will match the timestamp characters (or any other characters for that matter) and then since you are only pulling the 3rd group they will just get ignored like the day and month are.

Would anyone mind sharing their dictionary on this?  I can't get it to work.


Thanks!


Answer

Hi Bob!


If your original data is in this format, XX/XX/XXXX, do this:

For the Replacement Value type in:

$3

Press the “+” sign to add it to the list. Then highlight the “$3” that you added. Then type in this for the Value to match on:

(^\d+)/((?<=/)\d+(?=/))/(\d{4}$)

 

If your original data is in this format, XX/XX/XX, do this:

For the Replacement Value type in:

$3

Press the “+” sign to add it to the list. Then highlight the “$3” that you added. Then type in this for the Value to match on:

(^\d+)/((?<=/)\d+(?=/))/(\d{2}$)

 

If your original data has a time stamp, do the following:

For the Replacement Value type in:

$3

Press the “+” sign to add it to the list. Then highlight the “$3” that you added.

Then type in this for the Value to match on:

(\d{1,2})/(\d{1,2})/(\d{2,4}).*


These options will each pull out the 4 digit year from the original string. Make sure you select the “Use Regular Expression box” at the bottom.


Login or Signup to post a comment