Alteryx Designer Desktop Discussions

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

Help cleaning up commas in cells with varying numbers of commas

csh8428
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

 

CarsCars_fixed
, , , , , , , ,null
, ,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!

 

Craig

3 REPLIES 3
StellaBon
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. 

FinnCharlton
13 - Pulsar

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

FinnCharlton_0-1680793958323.png

 

DataNath
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:

 

DataNath_0-1680794098027.png

Labels