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

Tips and Tricks

Preparation

Accepted answers

MarqueeCrew

@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

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

hey @collinjd,

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

Remove certain special characters.yxmd

All comments

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

@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

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

hey @collinjd,

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

Remove certain special characters.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703