Regex , If String occurs once then Trim all punctuation, If Twice, use AND

[wonka1234]

Hi,

I am trying to use regex to solve this issue, can this be done? Left is my input, right is expected output.

, , , Met Current Validation	Met Current Validation
, Met Action Plan Validation, ,	Met Action Plan Validation
Met Action Plan, , Met Current Resolution,	Met Action Plan AND  Met Current Resolution
Met Action Plan, , Met Current Resolution, Met Date	Met Action Plan AND Met Current Resolution AND Met Date

[apathetichell · Accepted solution]

I got lazy here and used three formula tools - blah. someone else could have done this with one.

formula 1:

output

Regex_replace([input],"^\U{1,}","")

formula 2:

output

Regex_replace([output],"\U{1,}$","")

formula 3:

regex_replace([output],"\U{2,3}"," AND ")

[Prometheus · Accepted solution]

@wonka1234 You could do it with three expressions in one Formula tool. In my workflow, I called the field "Text." The first expression would be to find where a comma is followed by a space and replace it with two spaces: REGEX_Replace([Text], '(,\s)+', ' ')

The values that end up with leading and/or trailing spaces can be cleaned up using the Trim function:TRIM([Text])

After that, you'll have to find the double space between values and replace it with " AND ": Replace([Text], ' ', ' AND ')

 

[flying008 · Accepted solution]

Hi, @wonka1234 

FYI.

Spoiler

Replace(Trim(REGEX_Replace([Txt], '(,\s?)+', '|'),'|'), '|', ' AND ')

[caltang · Accepted solution]

Like so?