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
Solved! Go to 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