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

Using RegEx Tool (Tokenize) Text String Containing Special Characters

KevinK215
6 - Meteoroid

Good morning community,

 

I am having the hardest time writing an expression in RegEx to split a text string into three separate columns. The default format on the text string is Employee:  LastName, FirstName (123456)

I'd like to drop the "Employee: " piece of the string and parse the remaining into 3 columns- Last Name, First Name & Employee ID.

 

I was able to parse out the employee ID into a separate field by escaping out the parentheses but I can't seem come up with a complete expression that will accomplish all three steps at once. 

 

Any help would be greatly appreciated. 

2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

@KevinK215,

 

You can manage this through the regex tool, but I'm going to show you how to do it in a formula.  Let's define 3 new string variables (Last_Name, First_Name and Employee_ID) and here are the formulas to try:

 

Regex_Replace([Text_String],".*?\s(.*?),.*",'$1')
Regex_Replace([Text_String],".*?,(.*?)\s\(.*",'$1')
Regex_Replace([Text_String],".*?\((.*)\)",'$1')

The first formula looks for anything up to the first SPACE and then grabs everything until the first COMMA.  That is GROUP #1.  It then puts GROUP 1 as the output.

The second formula looks for anything up to the first COMMA followed by a SPACE and then grabs everything until the first SPACE followed by an OPEN PARENS.  That is GROUP #1 for the second formula output.

The third formula looks for anything up to the first OPEN PARENS and then grabs everything up to the last CLOSE PARENS.

 

Cheers,
Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
BenMoss
ACE Emeritus
ACE Emeritus

This is fairly simple.

I would actually put your regex tool in 'Parse' mode. You then specify your new columns with brackets.

Your regex statement would then look something like.

Employee: ([[:alpha:]]+), ([[:alpha:]]+) \((\d+)\)

In the output settings in the lower part of the configuration panel you can then name each of your 'groups' or new columns accordingly.

 

Ben

Labels