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

Regex or Text to Column

mustufa2019
8 - Asteroid

I am trying to do a text to column or Regex function to remove certain words from my fields. See below example:

 

Input

Name
Adam Smith
David Warner
Admin 1 Mark Donald
Admin 2 Alan Donald 
Mark Jones
Admin 3 Steve Smith

 

Output: I would like to either remove the "Admin" from my data or have them in separate columns. I shared two output examples that would work for me.

 

Output 1 (remove admin completely)

Name
Adam Smith
David Warner
Mark Donald
Alan Donald 
Mark Jones
Steve Smith

 

Output 2 (text to column)

NameName
 Adam Smith
 David Warner
Admin 1Mark Donald
Admin 2Alan Donald 
 Mark Jones
Admin 3 Steve Smith
10 REPLIES 10
KP_DML
8 - Asteroid

Hello,

Here is a RegEx solution: (admin\s\d+\s)

It matches "admin" + a single space + numeric characters (match as many as present in sequence) + a single space.

 

RegexAdmin.png

mustufa2019
8 - Asteroid

@KP_DML thank you so much, this solved it! I do however have another question. From the below, I am trying to remove key words (departments), e.g xample, infosec, technology, operations. I was thinking of doing text to column with delimter of "-" but i noticed some names have "-" between their first and middle name so it wont work. See below example: 

 

Input

Name

Steve Smith - InfoSec
Andrew Flintoff
ALEX-JAY Smith
Damien Martyn - Operations
Adil-Rashid Khan 
John Smith - InfoSec 
Matt Jordan - Technology

 

Output:

Name

Steve Smith
Andrew Flintoff
ALEX-JAY Smith
Damien Martyn
Adil-Rashid Khan 
John Smith
Matt Jordan
danilang
19 - Altair
19 - Altair

Hi @mustufa2019 

 

For your second question, you can use the same same technique as in @KP_DML's reply above and use \s-\s.* as the expression.   Space hyphen space followed by anything else.

 

 

mustufa2019
8 - Asteroid

@danilang Thank you for sharing, however this didnt seem to work. See below screenshots

 

mustufa2019_0-1573463103072.png

mustufa2019_1-1573463149782.png

danilang
19 - Altair
19 - Altair

Hi @mustufa2019 

 

In your screen shot above, where is your regex parse tool?

 

Dan

mustufa2019
8 - Asteroid

@danilang sure, please see below screenshot 

 

mustufa2019_0-1573525002314.png

afv2688
16 - Nebula
16 - Nebula

Maybe my solution is not as elegant but it works :S

 

(.*)(\s\-\s)(.*)

 

Untitled2.png

 

*On the replacement text I added   $1

 

Regards

mustufa2019
8 - Asteroid

@afv2688 wow this worked perfectly! Just one thing, I'm little new to Altteryx so was curious what is this (.*)(\s\-\s)(.*) expression stating?

 

Also, what does $1 in the replacement text? Without this, I noticed the text would disappear. 

afv2688
16 - Nebula
16 - Nebula

Hello @mustufa2019 ,

 

The expression means the following. If there are:

(.*) zero or more characters followed by

(\s\-\s) space character, dash character and another space character followed by

(.*) zero or more characters

 

Then take the first group only ($1)

 

I use the backslash to indicate I want to mean either a space character or the dash character

 

Regards

Labels