Regex confuses me. It’s a struggle to determine the characters necessary to identify patterns in text. (Although it is rewarding when it works!) To circumvent this dilemma, I utilize other tools.
I’m going to share my regex-less take on one of the Alteryx challenges that Bianca Ng tackles in her blog post here.
For Alteryx Challenge #211: Generating Email Addresses, Bianca uses the Regex tool to remove the number at the end of each record.
Her resulting workflow looks like this:
However, I chose to avoid regex.
Similar to Bianca, I started by splitting the one cell of data into rows based on the comma that separates the users.
Next, I split the one column of users into three columns by the delimiter of space. I chose three columns because some users have a middle name in addition to a first and last name.
Scrolling through the results, I noticed there was a N/A user.
Thus, I used a Filter tool to remove that record.
At the moment, the last names of users can be found in both columns 2 and 3. Hence, I updated column 3 to include all last names. I wrote a conditional statement in a Formula tool. If a cell in column 3 is null, replace it with the value in column 2 of the same record. Otherwise, keep the value.
Time to tidy up! I used a Select tool to remove two columns and rename the other two.
Now to remove that pesky number. Bianca used a Regex tool, but I used a Data Cleansing tool and selected Numbers.
I then added two formulas via the Formula tool.
The first is First Name Initial, in which I take the uppercase and leftmost character of First Name.
The second is Email in which I concatenate the previously created First Name Initial with Last Name and “@testemail.com”.
The output:
My last step is the same as Bianca’s. We both used the Summarize tool to group emails by the email group (based on the first letter of first names).
Ultimately, this is my workflow:
It's fun to see the variety of ways to arrive at the same answer in Alteryx. Nevertheless, I know I need to practice regex; I can follow in Bianca’s footsteps and take on more parsing challenges.