Help on splitting column data using Regex and Text to Column tools?

Hi there, I have been looking for a solution to split the column data I have into 5 different columns as shown in the image I have attached. It would be great if I can get some help on converting this string "GaaEe 2014 Death Only Z countries. Non smoker. Standard" into an expression that Alteryx understands to then be split into 5 columns. Any help would be greatly appreciated!

help_regex.PNG

Tips and Tricks

Custom Tools

Accepted answers

caltang

In that case, you don't need Regex or Text-to-Columns. Just use the filter tree method!

Splitting_column_data (1).yxzp

All comments

caltang

Please provide relevant data to this use case, and kindly provide your criteria in as much detail as possible. If you have a workflow built halfway, kindly export that over as well.

Qiu

@jakemiroquai
I think we need more information as mentioned by @caltang .

And maybe we can use the period "." as delimeter with tool of Text to Column?

jakemiroquai

Hi all @Qiu @caltang ,

I have attached a sample workflow showing the 2 rows of data which I wish to separate into its relevant columns; Name: GaaEe 2014, Type: Death Only, Country Type: A Countries etc etc...If you believe there is a better method, do advise! My idea was to use Regex to identify the string before inputting it into the Text to Columns tool to output the data into its correct fields. Thank you!

Splitting_column_data.yxmd

jakemiroquai

Currently, I am able to use the expression "(\S+ \d{4}) (.+?\s.+?) (.+?\s.+?) (.+?\s.+) (.+?.+)" for some rows of data but when it comes to data that vary such as the ones with paratheses, it is unable to read the expression. I was thinking there has to be a more dynamic way such that it can capture this variety and output the field data accordingly.

caltang

Due to the nature of your data, a straightforward solution via REGEX or Text-to-columns is not recommended.

I have done an example above where I had to go through many steps and processes to get the data in order.

It will help if you have a master list of variables from each column, then you can better prep the data.

Hope this gives you some ideas @jakemiroquai

Splitting_column_data.yxmd

jakemiroquai

This looks like something I could work with! It seems like I have to separate this 'separation of column data' process into different parts with the data that I have. Thank you for your insight!

caltang

No problem! If it helps, kindly like / mark as accepted solution if it helps.

jakemiroquai

I have attached a new workflow along with a text input showing my desired goal output. Thank you!

Splitting_column_data.yxmd

Sample_data.xlsx

caltang

In that case, you don't need Regex or Text-to-Columns. Just use the filter tree method!

Splitting_column_data (1).yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703