Parse string data from survey

Hi everyone

I need help parsing string data from survey responses. Typically I've used the Text to Columns tool but I think this data exceeds its limits on its own.

Essentially one of the responses includes commas within the selection that is not in brackets, quotes or parenthesis, so the response surrounding Black, Asian representation is split into different columns.

I've seen the RegEx tool being used in other posts but I'm not familiar with the expression syntax. Alternative was maybe using a parse-related function in Formula such as replace or contains??

Data set attached.

Many thanks,

Organisation Example Data.xlsx

Parse

Accepted answers

BrandonB

Maybe something like this that replaces the word "Has" with "|Has" so that you can use it as a delimiter:

Workflow is attached

Parsing.yxmd

All comments

BrandonB

Maybe something like this that replaces the word "Has" with "|Has" so that you can use it as a delimiter:

Workflow is attached

Parsing.yxmd

arundaka03

Hey @BrandonB. Any suggestions on how I remove the unwanted comma within my new column? Any comma which appears at the end of my string in the column.

Wanted to use Data Cleanse tool to remove punctuation but can't configure appropriately

TrimRight looks like a possibility but not clear to me how I would configure based on this: https://help.alteryx.com/20213/designer/string-functions#:~:text=Trim(String%2C%20y)%20%3A,order%20the%20characters%20are%20in.

arundaka03

No problem - solved with Fomula and TrimRight !

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335