Forum Discussion
Solved
Remove semi colon only when inside parentheses
I have data that contains multiple values within a single cell. I am attempting to separate this data, but occasionally there are additional semi colons throughout these values that are not delimiter...
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Hi,
Share some data to work with and show the expected result.
Thanks for the response and apologizes for not being clear in my initial post. The entire column contains cells similar to this:
Person A (Company XYZ); Person B (Company 123); Person C (Company A-1; Accounting); Person D (Company B12)
I would like to replace the instances where the semi-colon is inside the parentheses with a comma, to look like this.
Person A (Company XYZ); Person B (Company 123); Person C (Company A-1, Accounting); Person D (Company B12)
pls try this
= Text.Combine( List.Transform( Splitter.SplitTextByAnyDelimiter({"("})([Column 1]), (x)=> [ t1 = Text.PositionOf( x,";",Occurrence.First), t2 = Text.PositionOf( x,")",Occurrence.First), repl = if t1 < t2 and t1<> -1 and t2<> -1 then Text.Replace(x,Text.Range(x,t1,1),",") else x ][repl]),"(")or this code
Text.Combine( List.Transform( Text.Split([Column 1],"("), (x)=> [ t1 = Text.PositionOf( x,";",Occurrence.First), t2 = Text.PositionOf( x,")",Occurrence.First), repl = if t1 < t2 and t1<> -1 and t2<> -1 then Text.Replace(x,Text.Range(x,t1,1),",") else x ][repl]),"(")
