Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Parse name out with 2 different formats in a field

Highlighted
8 - Asteroid

Hello everyone, I'm brand new to Alteryx and I've been trying to recreate an some steps within Alteryx and one of those is parsing out a name from a Access field.  

 

I looked at this article and it got me most of the way:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/RegEX-Question-Name-Parsing/td-p/74014

 

But my problem is this.  My field can have the names formatted in  2 ways. IE: it can look like the below.

 

Name:

Jones, Billy

Billy Jones

Jones, Billy S.

So I'm trying to figure out how i can do all of this in 1 step.  I don't care about the middle name/initial but some of the fields have that too.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @ISUGraber!

 

Thanks for posting your question here.  I am going to apologize for answering it with a regular expression.  I am a big RegEx fan and what you're looking for is pattern matching.  I enhanced your requirement a bit as:

 

Please reformat an incoming name into "First Name" "|" "Last Name"

 

It will look for 4 patterns:

  • Last, First
  • First Last
  • Last, FIrst MI
  • First Middle Last

When it recognizes the pattern, it will create a pipe delimited result with FIRST|LAST.  From this point, you can use a text to columns tool to break apart the names.  You can do this within the formula tool, but it over-complicates the formula.

 

IF 
	REGEX_Match([Name], "(\w+),\s([\w\-]+).*")
		THEN REGEX_Replace([Name], "(\w+),\s([\w\-]+).*", '$2|$1')
ELSEIF
	REGEX_Match([Name], "(\w+)\s([\w\-]{2,})")
		THEN REGEX_Replace([Name], "(\w+)\s([\w\-]{2,}).*", '$1|$2')
ELSEIF
	REGEX_Match([Name], "(\w+)\s\w\s([\w\-]{2,})")
		THEN REGEX_Replace([Name], "(\w+)\s\w\s([\w\-]{2,}).*", '$1|$2')
ELSEIF
	REGEX_Match([Name], "(\w+)\s\w{2,}\s([\w\-]{2,}.*)")
		THEN REGEX_Replace([Name], "(\w+)\s\w{2,}\s([\w\-]{2,}.*)", '$1|$2')
Else [Name]
ENDIF

Try this with your data and see if you like the results.  When an UNKNOWN format happens, it simply outputs the NAME into the new field.  You might want to leave it as "" and put nothing into the field so that you can filter for these records.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
8 - Asteroid

That worked great!  Thanks!  I'm looking into Regex seems like it's something I should learn!!

 

Thanks for your help!

Labels