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
Cars | Cars_fixed |
, , , , , , , , | null |
, , | null |
, | null |
Corvette, Supra, Taycan | Corvette, Supra, Taycan |
Miata, , Corolla, , , Elanta, GTI | Miata, Corolla, Elantra, GTI |
Cayman, WRX, Civi Si, , | Cayman, WRX, Civic Si |
Thanks for any help!
Craig
Solved! Go to Solution.
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.
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: