Hello,
I am trying to separate data from one field with a long string in each row into separate fields. My data is taken from a txt file and looks like this:
10/02/19 SGR OP 193 1 |
09/12/17 SP ADL 1889 2 |
01/23/19 CHG ROG 3566 186 Room RG.204/AIST |
06/16/18 ALD SE RK 1655 1 Forwarded from OS1657888 |
I am able to separate out the text into different fields using the text to columns tool and using " " as my delimiter but I haven't found a way to separate out values while keeping the spaces between data like "ALD SE RK" or "Room RG.204/AIST" which I need keep together in one field.
I am new to Alteryx and I apologize if this is hard to understand. Any help would be appreciated.
Solved! Go to Solution.
Hi @jrysdon
The workflow I would use to accomplish what you post is the following:
With the "data cleansing" tool, I eliminate the leading and trailing spaces and the tabs:
Then, I use the RegEx tool, as follows:
The output is the following:
Attached you will find the workflow.
Regards!
Hi @jrysdon
You can separate based on patterns using RegEx in Alteryx. Because the consistent delimiter is multiple spaces, I'd replace those with another character (in the example here, I've chosen a hyphen), then use Text to Columns to separate on the hyphen.
With a formula tool, the expression Regex_Replace([Field],"\s\s+","-") will replace anything that's 2 spaces or more with a hyphen.
Let me know if that helps!
Cheers,
Esther
Thank you Julio, this has really helped.
Do you know how I would go about filtering out all the rows that start with a date?
Thanks
Hi @jrysdon
The date column, as parsed, isn't being treated as a date. You could use the Date Time tool to convert it into a date, if you'd want.
To filter on this column, I'd use a custom filter as follows:
Regex_CountMatches([Date field],,"\/")=2
That will filter where that column has 2 forward slashes within the field.
Cheers!
Esther
Hi @jrysdon
Did you mean filtering by a specific date, or simply filtering the rows having a date? If you meant the latter, @estherb47 gave you a pretty good solution!
Regards