Removing punctuation only at end

JamesBills

Hi,

Wanted to remove any punctuation or special characters at the start or end of the field. But leave it in the middle.

Fields vary in length and don't always end on a text character.

OLD NEW

LAGOS,\ LAGOS

RIO-BRAZIL RIO-BRAZIL
,NIGERIA,\LAGOS NIGERIA,\LAGOS
PHILLIPS66-5, PHILLIPS66-5

Thanks,

James

Regex

Parse

Accepted answers

DataNath

How's this @JamesBills? The expression basically will capture anything between the first and last instance of word characters (the \w notation).

(\w.+\w)

EDIT: I always forget that underscores are included in word characters and so if you had a record like '_,NIGERIA,\LAGOS_', this would throw off solutions that rely on the \w or \W notation alone. The following expression should address this as it includes _ in the non-capture along with non-word characters.

([^_\W].+[^_\W])

Remove punctuation from start+end.yxmd

All comments

binu_acs

@JamesBills one way of doing this with the replaceChar()

https://help.alteryx.com/20221/designer/string-functions

Replace_0816.yxmd

DataNath

How's this @JamesBills? The expression basically will capture anything between the first and last instance of word characters (the \w notation).

(\w.+\w)

([^_\W].+[^_\W])

Remove punctuation from start+end.yxmd

mceleavey

Hi @JamesBills ,

To do this you will need to specify that you are removing punctuation from the beginning and end of your string. I have built this in Regex to determine one or more none word character from the beginning of the string, OR one or more non-word character from the end of the string. You can change this if you are expecting numbers to be simply a manual character set if you like.

The regex looks like this:

^\W+|\W+$

Configure your regex tool to use the Replace function, and leave the Replacement Text section blank. This will effectively remove any matching strings:

This gives the following:

This will ONLY replace those non-word characters at the beginning and end of the string, and will replace ALL of them, not just one.

I hope this helps,

mceleavey regex solution.yxmd

philipmannering

@binu_acs I think you'd want to use the Trim() here to keep the stuff in the middle right? You can still give it the list of punctuation marks to remove.

Trim([Field], '!@#$^&*,/')

JamesBills

Thanks. Yes the second one works for me. Thank you all for quick responses

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703