How can I parse data that has the Escape square character in it?

[hydrogurl01]

Hi all,

I'm working with a dataset I am trying to parse out, but I'm not sure how to parse out this symbol. I've attached a screenshot of it below. There's the square Escape character and the number 3 I am trying to parse my columns by. Any ideas on how to do this?

[Thableaus]

Hi @hydrogurl01 

Could you please post a sample of your data? At least one line containing the square character.

We could extract the ASCII or Unicode Character of this square to remove it with regex or even regular replace function.

Cheers,

[hydrogurl01]

Hi @Thableaus ,

I've attached some sample data here. I've never used ASCII or Unicode before so any detail on that would be helpful!

[Thableaus]

@hydrogurl01 

Try this:

ReplaceChar([Field], CharFromInt(3), "")

This will replace the \u0003 unicode character - which stands for the End of Text. I suppose this is your square character.

Cheers,

[hydrogurl01]

Thanks @Thableaus ! This got rid of the squares, however I still do have the 3 in here. I can't delimit by the number 3 because I have 3's elsewhere in my data that need to stay there. Any advice on how to navigate around this?

Also would you be able to explain the CharfromInt(3)?

[Thableaus]

@hydrogurl01 

Try parse method with RegEX tool using this:

(.*?)\x{0003}3(.*?)\x{0003}3(.*?)\x{0003}3(.*?)

\x{0003} stands for the Unicode square character.

CharfromInt(3) is a String function that returns a string based on its Unicode decimal value (in this case, Unicode value for the weird square is 3).

Cheers,

[hydrogurl01]

HI @Thableaus ,

This is almost there! For some reason the last comments column isn't showing up with this regex expression. I tried copying/pasting the same expression again at the end but then everything blanks out and I still don't see the last comments column. Any insight into why that last column isn't showing up?

[Thableaus · Accepted solution]

@hydrogurl01 

Sorry:

(.*?)\x{0003}3(.*?)\x{0003}3(.*?)\x{0003}3(.*)

Change the expression, the last "?" character in RegEX is unnecessary.

Cheers,

[Thableaus]

@hydrogurl01 

Did it work?

Cheers,

[hydrogurl01]

Worked perfectly!! Thank you so much for all the help!