Alteryx Designer

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

Help: Parsing Value after last occurrence of character

Highlighted
5 - Atom

Hello,

 

I have a column in my data set that displays the data in the following format, per record:

 

Cat1|Cat2|Cat3|Cat4|Cat5|Cat6

 

With each section separated by the character, "|". Also, each category (1-6) is not a consistent character length, so I can't just do a formula to cut x-characters from the right, sadly.

 

 

My goal is to split each category into its own column.

 

I think the RegEx tool may be what I need to use, but I am not familiar with how to write an expression that would be able to do this. I've tried a few examples that I've found, but can't get them to work.

 

Thank you for your help and please let me know if you need any clarification. 

 

Thanks!

 

Tracy

Highlighted
Alteryx
Alteryx

Hi @Tracy84,

 

Thanks for your question! While you can use RegEx, you can also use Text to Columns which is quick and easy to set up. 

 

See the screenshot below for the configuration of the Text to Columns tool: Notice you are using the pipe (|) as the delimiter.

 

text_to_columns.png

 

Hope this helps!

 

Amelia

Highlighted
12 - Quasar

@Tracy84

 

Only because you mentioned RegEx as a solution, I figured I would offer up an option (Although I agree it's a bit overkill).

 

The simplest method is RegEx Tokenize. All you would need in the Regular Expression is "\w+". Set your Properties to either the number of columns (if your string is consistent) or rows, if it can be inconsistent length.

 

Note, this assumes that you have no non-word characters in your categories. If you do, then you would have to modify the regex pattern.

 

regex.PNG

Highlighted
5 - Atom

AmeliaG,

 

Oh, that makes sense, I didn't know that was an option - I'll try that, thank you!

Highlighted
5 - Atom

Neil, Thank you for this response as well, and now I know! ha :)

 

 

Highlighted
8 - Asteroid

You could also use a formula like this that takes advantage of the string functions ReverseString, FindString and Left. Then you don't have to deal w/ text to columns and/or RegEx

 

reverseString(left(ReverseString([Field1]),
FindString(ReverseString([Field1]), '\')))

 

For example, if I want to get whatever comes after the last \ in a string:

 

Field1

C:\Users\Downloads\Third\Nov_2019\filename.csv

 

The formula returns:

filename.csv

Highlighted
6 - Meteoroid

Beers on me, thank you.

Labels