More RegEx / formula help

[Brad1]

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.

[geraldo]

hy

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

[NickSm · Accepted solution]

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:

[geraldo]

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]

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

Thanks to all.

[Brad1]

JUST "Trim" does not do it.

[ronjosenthal]

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