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

More RegEx / formula help

Brad1
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.

6 REPLIES 6
geraldo
13 - Pulsar

hy

 

 

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

NickSm
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

geraldo
13 - Pulsar

Hi

 

 

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

Brad1
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.

 

Brad1
11 - Bolide

JUST "Trim" does not do it.

ronjosenthal
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