Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

RegEx to remove bracketed character from cells

Highlighted
6 - Meteoroid

I am attempting to remove some text from a cell containing sting values. I've had no luck finding an answer so I'll pose it to the community! 

 

I have an Excel input file that has a column containing names that have the potential to contain a [C] at the end and would like to remove it (RegEx replace or whatever works)

The manager names are in the format of First Name Middle Name (not always included) Last Name [C] (not always included). I've done everything short of a 'text to columns, formula to recognize and replace the [C], then rejoin' as I'm certain that there's a more elegant solution. 

 

If it makes any difference, I already have a RegEx  + formula that parses and rejoins the name into my preferred format. It is 

 

RegEx: ^(\w+)\W?((?<=\W).+(?=\W))?\W?(\w+)?$
Marked groups are
Mgr First Name
Mgr Middle Name
Mgr Last Name

 

Formula: REGEX_Replace([Mgr Last Name]+', '+[Mgr First Name], '^,|,$|,(?=,)', '')
 Output is 'Manager' in the format of Last Name, First Name

 

Any thoughts, RegEx warriors? I've included a stripped down version of my workflow to show what I've got so far. Bonus points for anyone who can help with the hyphenated last name staying together as well! 

Highlighted
Alteryx
Alteryx

Hi JFurda,

 

In order to remove the "[C]" from the Worker's Manager column you can simplify the Regex by just using an EndsWith() formula. This will allow you to recognize the "[C]" and then simply trim it out of your string with a substring() formula.

 

Here is the formula I used:

 

clipboard_image_0.png

 

 

I hope this helps!

Connor Kelleher
Commercial Sales Engineer
Alteryx
6 - Meteoroid

That works with the modification to remove the blank space between the last name and the [C]. The '3' length was leaving the blank space that threw off the parsing of middle and last names! 

 

Thank you! 

Labels