Remove certain special characters

[collinjd]

Hi There,

I am looking to remove 2 special characters which are " & '

I have used the data cleansing tool to remove punctuation, however, it removes a few characters that I require. I have

Current Data

'20130326.0018289187'
"PCHQ9N46M509B"

Required Results

20130326.0018289187
PCHQ9N46M509B

I have tried the formula tool and could not see how to remove these 2 characters only. Any suggestions would be appreciated, thanks :)

[Claje]

REPLACECHAR(REPLACECHAR([Yourfield],'"',''),"'","")

You could also do this in a multi-field formula tool where it would look like this:

REPLACECHAR(REPLACECHAR([_CurrentField_],'"',''),"'","")

That would allow you to apply these rules to multiple fields.

The trickiest part about this is the need to exclude both double and single quotes.  I'm not sure if there is a way in Alteryx to escape one of these characters.  If there is, you could remove the second REPLACECHAR() formula.

[MarqueeCrew · Accepted solution]

@collinjd,

I looked up the hexadecimal values in an ASCII table.  I then used them in a regular expression as:

REGEX_Replace([Current Data], "[\x22\x27]", '')

It works for your data.

Cheers,

Mark

[Claje · Accepted solution]

Shows what I know, saying that there's something I'm not sure how to do in Alteryx!

This solution is smart, and makes the formula much easier to read/maintain.

EDIT:

On a hunch, I just tried something else that worked for me.

REPLACECHAR(Field1,"'"+'"','')

By using the + to concatenate the two strings, you can replace both characters using only one formula.

Whether you use RegEx or ReplaceChar is pretty much personal preference and/or based on what you find more readable, I would say.

[Syarifhidayat · Accepted solution]

hey @collinjd,

I share you my workflow that may help with your problem. Please take a look.

[collinjd]

Thanks all for the help!

[collinjd]

Great thanks very much!

[Bria_Davis]

This was exactly the solution I needed for a database export that had everything between ="xxx". It was a dynamic field range so I needed a solution that would clean up all future added fields as well without having maintenance.