Removing punctuation only at end
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@JamesBills one way of doing this with the replaceChar()
https://help.alteryx.com/20221/designer/string-functions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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], '!@#$^&*,/')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks. Yes the second one works for me. Thank you all for quick responses
