Parse Error: Invalid character reference

[Billy_Jones]

Hi all, I hope you're well.

I'm having a really tricky time trying to separate out some data which uses '' as a delimiter.

The data is responses to a multiple choice survey question, and unhelpfully the different possible responses are denoted by a number from 1-26, So I'm not sure how to flag a certain field as containing a '1' without also flagging every field that includes a '10','11' etc. One such example field looks like this:   121019. Example of a field also included below as image

The aim here is for me to create a new field for each of the 26 possible responses, to flag them as a yes/no (or in this case 1/0) response against the record 

My solution for this was to try and use text to rows to split the data out, and then flag responses accordingly for each row from there. but if I try to use  as a delimiter I get a message 'Parse Error: Invalid character reference' have tried \s and other examples for delimiters too incase they worked, but no luck there either

My guess is that can probably be resolved using an expression in RegEx. But unfortunately that's where the limitations of my Alteryx knowledge currently ends!

If anyone has any suggestions for parsing this data I would be really grateful, Thank you!

Billy

[FinnCharlton · Accepted solution]

Hi @Billy_Jones 

I would try replacing the unknown character using regex, then using text to columns:

[Billy_Jones]

Thank you @FinnCharlton. I thought that had solved it for a moment! 

But it looks like it's also now split out 2 digit numbers into single digit numbers

[OllieClarke · Accepted solution]

Hi @Billy_Jones 

Based on what you've pasted in the question, the special character is an 'end of transmission block'. This has the ascii code 23, so the following formula should allow you to replace them with pipes (like @FinnCharlton suggested), before text to columnsing on pipes

Replace([Field1],CharFromInt(23),'|')

Hope that helps,

Ollie

[FinnCharlton]

Hi @Billy_Jones , this method is working fine on my example, maybe check the configuration of your tools? You could try replacing the text to columns tool with a RegEx tool set to "Tokenize", then use \d+ as the regular expression.

[Billy_Jones]

Thank you @OllieClarke and @FinnCharlton !

I really appreciate your help with this, It looks like your solutions have helped sort this out!