Forum Discussion
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 delimiters. I would like to replace all semi colons with commas, ONLY when the semi colon is inside parentheses. Any suggestions?
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
11 Replies
In Power query replace ";" with ,
See screen print
I am an unpaid volunteer helper seeking kudos.Please show your thanks by clicking the Accept Solution button and the thumbs up button to leave kudos.
Thanksif there is only a semicolon inside the parentheses then it is simple.
show an example so we can help you
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]),"(")
