cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | Looking to expand your Alteryx skillset?! Check out the latest set of interactive lessons in Alteryx Academy: Creating Analytic Apps
SOLVED

Remove 'n' number of characters from the end of a string

I have a column in excel the content of which are something like this:

 

Column

COI ValueA 11.2.0.4.0 06-JUL-2015 13:00:01

ICT WAK RGTT ValueB 3.2.0.4.0 02-APR-2012 13:57:42

 

As you can see above there is some data followed by a timestamp. I would like to remove this timestamp.

 

Output:

Column

COI ValueA 

ICT WAK RGTT ValueB

 

Options available in data cleansing tool does not allow me to specify the number of characters I want to trim, same applies for formula tool I cannot specify the number of characters that I want to remove.

 

Please help. Thanks.

Magnetar
Magnetar

You could do a Formula with something like:

Left([FieldName], Length([FieldName]) - 21)

... effectively removing 21 characters, including the 20 for the full date-time timestamp, plus the preceding space.  This assumes a consistent 20 characters for the timestamp.

I'd probably do @JohnJPS answer but as an alternative you can use a REGEX_Replace:

REGEX_Replace([Column],"^(.*) ([0-9\.]+) (.{20}$)","$1")

This will get the part before the version number.

 

If you use a Regex tool in parse mode with the 

^(.*) ([0-9\.]+) (.{20}$)

expression you can break it into three fields: Name, Version, TimeStamp

Labels