Seperating irregular data

[jrysdon]

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.

[JulioMO]

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!

[estherb47]

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

[jrysdon]

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

[estherb47 · Accepted solution]

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

[JulioMO]

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