Remove semi colon only when inside parentheses

[chadnelson]

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?

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[chadnelson]

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)

[Ahmedx]

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]),"(")

[chadnelson]

Unfortunately that didn't work. It returned the following error:

"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RixzOI0gpuWV6jNLW?e=gWIFbE

[chadnelson]

This worked! Thank you, so much.

= Table.AddColumn(#"Changed Type", "Custom", each 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.ReplaceRange(x,t1,1,",") else x ][repl]),"("))

[Ahmedx]

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]),"(")

[Ahmedx]

sorry this code corrected

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.ReplaceRange(x,t1,1,",") else x ][repl]),"(")

[chadnelson]

It is still giving me the same expression error.

[Ahmedx]

if there is only a semicolon inside the parentheses then it is simple.

show an example so we can help you

[speedramps]

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.
Thanks