I think this is a simple regular expression but I haven't attempted one in several months so I'm completely blanking out. I need to convert a YYYYMM number field (from NCOA return-of-address file) to a date format of MM/DD/YYYY (with DD = 01). I don't think the forward slashes are necessary, either.
We had a similar problem - we needed to convert YYYY-MM-DD to DD/MM/YYYY. This is what worked for us in the end:
Value to match on: ([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) Replacement Value: $3/$2/$1
(Credit to Kevin Morton, RegEx King).
T
Tim Beggs
said
over 7 years ago
I have a similar format that I need assistance with. What regular expression should I use to convert: YYYY-MM-DD 00:00:00.000 to MM/DD/YYYY
Thanks, Tim
J
John Shephard
said
almost 6 years ago
Hi Becky,
Can you double click in your values to match on and make sure there is no extra space at the end? When I copied and pasted the formulas from the post it put a space on the end which causes it not to function properly.
Thanks!
John
B
Becky Griswold
said
almost 6 years ago
That was it!! Thank you so very much. :)
B
Becky Griswold
said
almost 6 years ago
I'm trying to format from this: YYYYMMDD to MM/DD/YYYY using regular expressions. I found the following, however when I test it, the computed text field is blank. Any suggestions on how to make it work?
17. Changes date format from YYYY/MM/DD to MM/DD/YYYY:
Replacement Value: $2/$3/$1 Value to Match On: (^\d{4})/((? Thanks in advance, Becky
J
John Shephard
said
almost 6 years ago
Hi Becky,
Does the incoming data have slashes (like 1985/06/24) or is it missing the slashes (19850624)?
If it has slashes this will work: Replacement value: $2/$3/$1 Values to match on: (^\d{4})/(\d{2})/(\d{2}$)
If it does not have slashes this will work: Replacement value: $2/$3/$1 Values to match on: (^\d{4})(\d{2})(\d{2}$)
Thanks!
John
B
Becky Griswold
said
almost 6 years ago
No slashes.... so the data in the csv file looks like this: 20160509.
I applied the formula below and the test is not adding the slashes.
Thank you for the quick reply!!
N
Nic Bourne
said
almost 10 years ago
Patrick,
This should do the trick.
Replacement Value: $2/01/$1 Value to match on: (\d{4}(?=\d{2}))(\d{2})
Patrick Manning