community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

How to use RegEx to remove text in a string and replace text with the updated expression

Highlighted
Meteoroid

I have a date field coming in from a csv file that stores the dates as '2013-12-11T05:18:37.419+00:00' (V_WString type) and I need to get the date to look like '2013-12-11 05:18:37'. I know the RegEx Tool has the capability to do this, but I cannot for the life of me figure out how to set up the regular expression. Please Help!

Alteryx Certified Partner
Alteryx Certified Partner
Replace(left([Input Date],19),"T",' ')
regex_replace([Input Date],"(.*)T(.*)\..*",'$1 $2')

The first formula takes the left 19 characters and then replaces the T with a space.

 

The second formula takes everything up to the T and everything between the T and the Period and puts a space between those groups.

 

Cheers,

Mark

 

https://community.alteryx.com/t5/Alteryx-Analytics-Excellence/Excellence-Awards-2017-Adam-Rant-From-...

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteoroid

Thanks for the quick response! Both options worked. Using the RegEx tool the Regular Expression would be (.*)T(.*)\..* with the output method as Replace and the Replacement Text set to $1 $2. However, if the letter is not always T how would one account for any random letter?

Bolide

Here is an other option - break out into groups and then replace with group 1 SPACE 3

 

(^.+)(T)(.+)(\.)(.+$)

 

(^.+) everything from beginning of the line

up to (T) 

(.+) everything after T

up to the (\.) literal period

(.+$) and everything after period - technically this could be be in period group.

 

... random letter group would be  ([a-zA-Z]) ...eg any of those single letters. You could even add a space ([a-zA-Z ]) should the T be a " " 

(^.+)([a-z ])(.+)(\.)(.+$)

 

Capture.PNG

Alteryx Certified Partner
Alteryx Certified Partner

If you don't know the letter, you could use:

 

\u  is uppercase letter

\l   is lowercase

 

lots of options :)

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Moderator
Moderator

This also can be done with the new DateTime Parse tool in Alteryx 11 using the "Custom" format:

 

yyyy-MM-ddThh:mm:ss

datetime.png

Sophia Fraticelli
Premium Support Advisor
Alteryx, Inc.
Labels