Removing Excess Comma's in an Concatenated Field

[smoskowitz]

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

[MarqueeCrew · Accepted solution]

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?

[smoskowitz]

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

Thanks,

Seth

[MarqueeCrew]

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.

[smoskowitz]

This is genius! Thank you!

[brad_j_crep]

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]

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