Splitting columns based on multiple rules

[apoorvsingh]

Hello,

I was wondering if there is a way to split a column into multiple columns by using multiple constraints. Let me give an example as this statement sounds very ambiguous. 

If the column looked like this 

Total Score - Penn

30-Day All-Cause Reup (ERP/ECRP)

30-Day All-Cause Reup(Med)(ERP/ECRP)

Patient Experience - ERP Overall 

Total Score - Park 

Is there a way I can split the 1st, 4th and 5th row by the dash in the middle but the 2nd and 3rd row by the first "(" ? If there is a character finding tool or anything that can help me I would really appreciate it. 

Thanks

[danilang · Accepted solution]

Hi @apoorvsingh 

The attached workflow add a preprocessing Formula tool before the split with the following 

if Contains([Field1],"(") then
      Replace([Field1], "(", "|")
else
      Replace([Field1], "-", "|")
endif

If there's an Open Bracket replace it with a pipe "|"

otherwise replace the hyphen with a pipe 

Then you split on the pipe character.  I use "|" as a generic replacement character because it's almost never found in standard text.

You can build out this formula to handle any complex rules you may have.  

Dan