Split Column_Separate Company & State

[Hongli1216]

I'm trying to split the company code and state in attached column A to column B, company code,  and C, state. Please advise! Appreciate your help! Thanks!

Holly

[T_Willins · Accepted solution]

HI @Hongli1216,

The following RegEx parse formula will give you the results you are looking for:   (\w*\.*\d+)_*(.*)

First group

\w*     Zero or more word characters

\.*      Zero or more periods (\ needed to escape special character, otherwise .* = zero or more of anything)

\d+    One or more numbers

Between groups

_*    Zero or more underscore

Second group

.*     All remaining characters

[TonyA]

This covers the use cases you showed, but it expects that the code starts with zero or more letters and ends with numbers possibly including other characters between the letters and numbers. It also allows for an optional "_" between the code and the state name. It would need to be tweaked if this pattern isn't followed.

[Hongli1216]

T_Willins,

It's so great! It works! I really appreciated your timely help! What should I learn to know how to write this formula? The instruction online sometimes confused me. Please advise! Thanks a lot!

Holly

[T_Willins · Accepted solution]

Hi @Hongli1216,

For RegEx Perl Syntax assistance I would recommend starting with this guide:  https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/RegEx-Perl-Syntax-Guide/ta-p/1288

To learn more about RegEx and other Alteryx tools, I have found working through the Weekly Challenges, the Learning Paths and the Interactive Lessons very helpful.  All of these are under the Academy tile.

[Hongli1216]

Hi T_Willins,

Thanks for your guidance! I will try to follow it! You have a relaxed long weekend!

Holly