Need help with regex parse for the following data

[moinshaik]

MUNICIPAL & FOOS     -12,345,678.30      -6,789,123.23    0.00     -129,000.79      -143.43

TETOL FOON     GLASS 01     GLASS 02    GLASS 03    GLASS 04

I would like to regex parse the above data.. I need to have the data in 6 columns first one and 5 columns in second one. I am trying with the regex parse but I am getting error. 

[vizAlter]

Hi @moinshaik — Can you attached the sample data - Input and desired output, both?

[moinshaik]

@vizAlter pfa

[vizAlter · Accepted solution]

Hi @moinshaik   — Hope attached the solution will be helpful, if not let me know.

Write this expression in a Formula tool:

REGEX_Replace([Field1], "[^\S\r\n]{2,}", ";")

[Jonathan-Sherman · Accepted solution]

Hi @moinshaik,

I would first use a Regex_Replace() function to replace groups of two or more spaces with a pipe '|' which can then be used as a delimiter with a text to columns tool:

REGEX_Replace([Input], '\s{2,}', '|')

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[estherb47 · Accepted solution]

Hi @moinshaik 

Another solution is to parse into rows using Regex Tokenize method. Here, I start with a Record ID tool to identify the unique rows. Then, the RegEx Parse:

This is telling Alteryx to separate into rows when it encounters at least 2 spaces, or the end of a line. the (?: is an unmarked group, that defines the separator for the data.

I love using the Tile tool to assign unique rows within a group, so that's what I've done here. 

And then a crosstab tool  to rebuild your table.

Please let me know if this helps.

Cheers!

Esther