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
Gelöst! Gehe zu Lösung.
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
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
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 😀👍
@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
@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🙂
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