Alteryx Designer Desktop Discussions

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

Help cleaning up commas in cells with varying numbers of commas

10 - Fireball

I have a field that has a series of strings separated by commas and spaces. Sometimes the field is ONLY commas and spaces.

The field has an unknown number of strings, commas, and spaces


What I'm trying to do is come up with the most efficient means of cleaning the cell to meet these 2 output conditions:

1:If it's ONLY commas and spaces then the cell should be null.

2: If it is a series of strings, then each string should be separated by a comma and a space.


If possible I would like to do this using the multi-field formula tool or transpose/crosstab as I need apply the same logic to multiple fields


I'm guessing the first thing to do would be to remove the spaces.


Here's a sample of what I'm taking about


, , , , , , , ,null
, ,null
Corvette, Supra, TaycanCorvette, Supra, Taycan
Miata, , Corolla, , , Elanta, GTIMiata, Corolla, Elantra, GTI
Cayman, WRX, Civi Si, ,Cayman, WRX, Civic Si



Thanks for any help!



11 - Bolide

You can use the Text to Columns tool to parse everything into separate columns, using a comma and space as the delimiter, then use a formula to concatenate your separated columns into one again. 

13 - Pulsar

Hi @csh8428 , here's a RegEx formula you can use in a multifield tool that will solve your problem:



17 - Castor

Hey @csh8428, here's a formula you could try - should work the same in Multi-Field, just replace the field name with the [_CurrentField_] argument: