Regex Question - Breaking a column with variable results into 2 columns

[DanielG]

Hi,

I have a column of data that looks like "Original Field" below.  In my actual data, the 2 character codes will vary from row to row, but no 2 character code will repeat in the same field

The yes's and no's will also vary, as well as the total number of segments, i.e. one row of data could just be "No - GR" or it could be upwards of 10-12 segments in some instances.

I am looking for a Regex solution to pull the No items out into one column and have a mimic of that process that puts the Yes items into another column.

Original Field	Yes Column	No Column
Yes - GP, No - GR, Yes - WS, Yes - JL, No - MP, Yes - MR	Yes - GP, Yes - WS, Yes - JL, Yes - MR	No - GR, No - MP

I am not sure how to pull this one off.

Any suggestions would be greatly appreciated.

Thanks!

[echuong1 · Accepted solution]

An easier way to do this is with text to columns and a cross-tab.

I added a record ID to know which line the records originally came from, in case there are multiple in your actual dataset. From there, I used text to columns to split the values into separate rows, and then to parse out the No/Yes. A data cleansing removed leading/trailing spaces. I used a cross-tab to pivot and concatenate the values back.

[DanielG]

Awesome.  Thanks!

This is a huge help.  🙂

[danilang · Accepted solution]

Hi @DanielG 

My Regex-Fu isn't string enough to do it all in a single statement, but here's a formula tool that creates your two fields by removing the opposite values,  i.e. the Yes values are removed to form the No string

Dan

[DanielG]

That is why I love this tool.

Multiple ways to get the same thing done.

Thank you both!