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

[binuacs]

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

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

[DataNath · Accepted solution]

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

[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,

M.

[PhilipMannering]

@binuacs 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