Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

simpleminded
7 - Meteor

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.

4 REPLIES 4
JohnJPS
15 - Aurora

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.

jdunkerley79
ACE Emeritus
ACE Emeritus

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

ferngl2000
5 - Atom

Thanks - great and easy solve.  Creative!

jacob_kahn
12 - Quasar

Thank you! This was very helpful!

Labels