Parsing String into 4 Columns

[RickRick9901]

Hello all,

I am new to Alteryx and I am trying to split a string of numbers and letters into 4 columns using Regex. Below is an example:

US463-13W

The 4 columns would read "US", "463", "13", and "W".

In the data set, there are varying abbreviations of countries in the beginning as well as single and double digit numbers after the "-". Unfortunately I am having a lot of trouble with this.

Any help would be appreciated. Thank you

[atcodedog05]

Hi @RickRick9901 

would you able to provide more sample scenarios.

[PhilipMannering · Accepted solution]

Hi Rick,

You can use the Regex Tool set to the Method Parse

Use the pattern,

(\u+)(\d+)-(\d+)(\u+)

This will work for your example, and hopefully other examples as well! If not, share some data and we can fix it. 🙂

Thanks,

Phil 

[estherb47 · Accepted solution]

Hi @RickRick9901 

For the initial letters, you can try (\D+?) - that captures all non numbers up until the first number

Then (\d+?) for the numbers

You can use - as a separator outside of the parenthesis

then (\d+?) again for the numbers

And (\D+) for the closing letters.

So, parse method: (\D+?)(\d+?)-(\d+?)(\D+)

let me know if that helps. Cheers!

Esther

[atcodedog05 · Accepted solution]

Hi @RickRick9901 

Here is the regex tool config

([[:alpha:]]*)(\d*)-(\d*)([[:alpha:]]*)

[[:alpha:]]*  one or more alphabets 

\d* one or more digits

Should work for most the scenario.

Output:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[estherb47]

@PhilipMannering , nice! Looks like we are on the same wavelength.

@RickRick9901, both Phil's and my solutions should work for you. If using Phil's, be sure that you set to case sensitive in the regex parse tool.

Cheers!

Esther

[atcodedog05]

@estherb47 interesting this the first time i seeing someone use \D for non digit. We have \W in the list for non character. Its the first time i am coming across \D.

Great new exposure🙂

[estherb47]

Thanks, @atcodedog05 . I prefer \D in this case, because we're not looking for a non-word character (\W wouldn't find letters either), we are looking specifically for a non-number.

Cheers,

Esther