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.
Solved! Go to Solution.
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:
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
That worked great! Thanks! I'm looking into Regex seems like it's something I should learn!!
Thanks for your help!