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 |
Solved! Go to Solution.
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.
@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 |
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.
Maybe my solution is not as elegant but it works :S
(.*)(\s\-\s)(.*)
*On the replacement text I added $1
Regards
@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.
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