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

Question

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 FieldYes ColumnNo ColumnYes - GP, No - GR, Yes - WS, Yes - JL, No - MP, Yes - MRYes - GP, Yes - WS, Yes - JL, Yes - MRNo - GR, No - MP

I am not sure how to pull this one off.

Any suggestions would be greatly appreciated.

Thanks!

echuong1 · Accepted Answer

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.

danilang · Accepted Answer

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