Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Removing Excess Comma's in an Concatenated Field

smoskowitz
12 - Quasar

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

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus
You could try:

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?
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
smoskowitz
12 - Quasar

Wow! That worked! Now ask me if I understand why it worked.

 

Thanks,

Seth

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
Regex is for pattern matching. It is well worth investigating. We are looking for a comma that exists 2 or more times. That pattern is: ,{2,}

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.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
smoskowitz
12 - Quasar

This is genius! Thank you!

brad_j_crep
8 - Asteroid

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,}.

davidhenington
10 - Fireball

You're good @MarqueeCrew , very, very good! 

Labels