Removing Excess Comma's in an Concatenated Field
- 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
Hello --
I am working on a project and have a challenge that is eluding me. I have concatenated about 7 fields using a comma (,).
1) ,,,,CBC500,,,,CBC501,,
2) ,,,,,CBC100,,,,,
How do I keep a single comma to delineate, but remove any unnecessary commas. So for item 1, I want to see:
CBC500,CBC501
Thanks,
Seth
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Regex_Replace([field],",{2,}",',')
That gets rid of repeating commas. To get rid of the first and last commas:
Trim(Regex_Replace([field],",{2,}",','),",")
Does that work for you?
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Wow! That worked! Now ask me if I understand why it worked.
Thanks,
Seth
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It could also be written as: ,,+
When alteryx finds it, it will replace it with the string of a comma.
Trim will remove leading and trailing white space by default. You can also specify a string to remove.
I combined the two functions in a nested statement.
Sorry for the iPhone short explanation, but I'm answering mobile.
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is genius! Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
could the same formula be used to get rid of multiple words? I have a concatenation which ends up with OCC,OCC,,,,OCC, . I'd like to get rid of the commas (which your regex does, thank you), and I'd like to get rid of the repeating OCC. Would it be something like \w(OCC){2} in the statement instead of ,{2,}.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You're good @MarqueeCrew , very, very good!
