Regex or Text to Column

[mustufa2019]

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)

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

[KP_DML]

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.

[mustufa2019]

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

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]

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

[danilang]

Hi @mustufa2019 

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

Dan

[mustufa2019]

@danilang sure, please see below screenshot 

[afv2688 · Accepted solution]

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

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

*On the replacement text I added   $1

Regards

[mustufa2019]

@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 · Accepted solution]

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