Start a new topic

Regular Expression: Numbers-to-Date

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).



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
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

That was it!! Thank you so very much. :)

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
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

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!!

 

image

image


Patrick,

This should do the trick.

Replacement Value: $2/01/$1
Value to match on: (\d{4}(?=\d{2}))(\d{2})
Login or Signup to post a comment