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

Parse name out with 2 different formats in a field

ISUGraber
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.

2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
ISUGraber
8 - Asteroid

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

 

Thanks for your help!

Labels