community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

Regex or Text to Column

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
Meteor

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

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
Nebula
Nebula

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.

 

 

Asteroid

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

 

mustufa2019_0-1573463103072.png

mustufa2019_1-1573463149782.png

Nebula
Nebula

Hi @mustufa2019 

 

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

 

Dan

Asteroid

@danilang sure, please see below screenshot 

 

mustufa2019_0-1573525002314.png

Alteryx Certified Partner
Alteryx Certified Partner

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

 

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

 

Untitled2.png

 

*On the replacement text I added   $1

 

Regards

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. 

Alteryx Certified Partner
Alteryx Certified Partner

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