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
K
Kristen Gajdica
said
over 4 years ago
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.
Would anyone mind sharing their dictionary on this? I can't get it to work.
Thanks!
K
Kristen Gajdica
said
over 4 years ago
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.
A
Amanda Allen
said
over 7 years ago
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
A
Allison Bolduc
said
over 7 years ago
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.
S
Sara Kramer
said
over 7 years ago
Does something need added to the code if the date is brought in with a time stamp?
Thanks, Sara
T
Therese Morris
said
almost 7 years ago
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}$)
A
Allison Bolduc
said
almost 7 years ago
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
T
Therese Morris
said
almost 7 years ago
Thank you! That worked perfectly!
W
Wayne Pozzar
said
almost 7 years ago
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.
Therese Morris
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
- Oldest First
- Popular
- Newest First
Sorted by PopularBob Parks
Would anyone mind sharing their dictionary on this? I can't get it to work.
Thanks!
Kristen Gajdica
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.
Amanda Allen
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
Allison Bolduc
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.
Sara Kramer
Thanks,
Sara
Therese Morris
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}$)
Allison Bolduc
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
Therese Morris
Wayne Pozzar
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.
-
Dictionary Issue- RegEx to translate dollar amount to specific letter code
-
Preventing IoM from importing Blank Attributes
-
Dictionary default United States?
-
How to not add a gift attribute if the import cell is blank
-
RegEx to remove leading characters
-
Credit Card type Dictionary
-
Regular Expression to pull a specific word or string from a longer string
-
Object reference not set to an instance of an object Error
-
Event Participant importing question
-
Date Dictionary Help: YYYYMMDD to MM/DD/YYYY
See all 1188 topics