Help: Parsing Value after last occurrence of character

[Tracy84]

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

[AmeliaG]

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.

Hope this helps!

Amelia

[neilgallen]

@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.

[Tracy84]

AmeliaG,

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

[Tracy84]

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

[dmccandless]

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

[Davidmg1982]

Beers on me, thank you.

[mselmonosky]

Best Formula ever!!!!  Thanks!!!