community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Remove certain special characters

Asteroid

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 :)

 

 

Magnetar
Magnetar

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.

Alteryx Certified Partner
Alteryx Certified Partner

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Magnetar
Magnetar

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.

Highlighted
Alteryx Partner

hey @collinjd,

 

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

Asteroid

Thanks all for the help!

Asteroid

Great thanks very much!

Labels