RegEx Parse Help

Hello - I'm trying to parse these fields into 5 columns using RegEx, however, sometimes the data in column 5 is blank. The spacing between each field is not consistent as its a PDF converted to text.

sdfrgh Doe, Jane Yes Sales Rep I Disney-Land

123566 Smith, John No Consultant Disney-World,Disney-Land

12dvs6 Smith, Jane Yes Sales Rep

Desired Output

sdfrgh	Doe, Jane	Yes	Sales Rep I	Disney-Land
123566	Smith, John	No	Consultant	Disney-World,Disney-Land
12dvs6	Smith, Jane	Yes	Sales Rep

Regex

Accepted answers

All comments

Qiu

@elclark
It appears to be difficult for me to tell the diifference between last 2 columns.
Any chance that the last column always starts from Disney or null?

flying008

Hi, @elclark

A dynamic resolution for you :

And if your first line of input isn't the field name, it's easier to deal with.

录制_2024_02_28_09_27_23_680.gif

Gawa

hi @elclark

As @Qiu says, it is difficult to parse the last 2 columns. My idea is:

First, parse data by the following Regex. You will get total 4 columns(last 2 columns are still combined)

In the 3rd column, value should be Yes or No so its nature can be used as a good anchor in regex expression.

^(.+?)\s{2,}(.+?)\s+(Yes|No)\s{2,}(.+)$

Second, parse the 4th column by Text to Column tool into 2 columns, and you will get the desired output.

1245181_gawa.yxmd

flying008

Hi, @elclark

or parse by the regex to 1 step:

Spoiler

^(.+?)\s{2,}(.+?)\s+(Yes|No)\s{2,}((?:\w+\s)*\w+)($|\s{2,}.+?$)

elclark

@flying008 the dynamic resolution almost worked, the scenario I ran into is sometimes the name section is blank but the format is consistent, unique id followed by name (or a blank space where the name should be) and then followed by either No or Yes (and this field is never blank and only those options).

flying008

Hi, @elclark

Maybe you can upload the fail case data for test.

elclark

@Qiu The last column always starts with 1 of 9 locations (so like Disney-xxx, Paris-xxxx, Rome-xxx, etc, those 9 locations are always the same but what follows after the - can be different) or its Null

Qiu

@elclark
I have to go through a lot to deal the last two columns and I hope your data is not so big since I am using Append tool.

@gawa

Maybe you have better idea? 😁

0229-elclark.yxmd

Gawa

@Qiu This was my best effort😂

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/RegEx-Parse-Help/m-p/1245191#M309968

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703