Parse name out with 2 different formats in a field

ISUGraber

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.

MarqueeCrew

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