Alteryx Designer

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

More RegEx / formula help

Highlighted
11 - Bolide

I've got a field that looks like this:

 

Field1

1,2,3,4

1,,,

,,,,

1,,3,

1,2,3,

,,,4

,2,,

,,3,

 

It needs to look like this:

 

Field1

1,2,3,4

1

 

1,3

1,2,3

4

2

3

 

I feel like a fancy RegEx formula is called for - that I can put in formula tool.  Thanks in advance for any help.

Highlighted
Alteryx Certified Partner

hy

 

 

use in the formula tool "Trim ([Field1], ',')"

Highlighted
Alteryx
Alteryx

Hey again @Brad1 

 

Seeing as there's multiple commas that could be either on the left, right, or within, a text to rows / pivot might actually be the most dynamic way to do it so that all possible scenarios are covered.

 

Other ways would be a combination of a TrimLeft, TrimRight, and Regex to replace multiple commas if they occur together, workflow that shows both:

 

clipboard_image_0.png

Highlighted
Alteryx Certified Partner

Hi

 

 

did you think that a lot of formula is a valid answer and you solve it in just one command Trim ([Field1], ',') Congratulations

Highlighted
11 - Bolide

in the formula tool I went with "Trim".  This knocked off front and back commas.  Then went with Replace ',,' [field] ','

 

Thanks to all.

 

Highlighted
11 - Bolide

JUST "Trim" does not do it.

Highlighted
5 - Atom

Here's my solution -- I trimmed leading and trailing commas and then used a regex_replace formula to change double commas to single commas.

Labels