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.


This should do the trick.

Replacement Value: $2/01/$1
Value to match on: (\d{4}(?=\d{2}))(\d{2})
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

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,
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}$)



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




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.



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

Login or Signup to post a comment