Replace character with some exceptions

[sayyedkohler]

Hi folks,

I want to replace commas with some other characters in Power Automate using the replace function but with some exceptions,
Check the below string,

P001234-LV,\"PER TRANSFER TRIM, LEVER HANDLE\",A&B_Product,\"User, Name\",

I want to replace green-colored commas with some other chars, while I do not want to replace red-colored commas (Those commas, which are covered inside \" characters).

Note: My data is dynamic, The main logic that I want is that it should only ignore commas that are between these two \"...\"

Please help me brilliant minds,

Thank You

[wskinnermctc]

(Should reconsider using color as primary identifier due to some people being color blind or different screen visuals. And the colors are intuitively backwards to say that Green is replace and Red is keep. Red is usually bad, Green is good. Keep Green. Replace Red.)

I'd like to help because these are fun, but can you give some more context. What are you trying to achieve? Like what should the final output look like with the replacement characters?

P001234-LV,\"PER TRANSFER TRIM, LEVER HANDLE\",A&B_Product,\"User, Name\",

Comma Positions

Where is this string coming from?

Will there always be 6 commas total in the string? Or can it vary?

Will there always be 4 \ slash in the string? Or can it vary?

I think about this as splitting the text string into 4 sections. This can be done by using the split() expression. If we split the the string by \" then it will have an output array.

I made an example variable named TextString and used the split() expression:

split(variables('TextString'),'\"')

The output of this expression will be:

[
  "P001234-LV,",
  "PER TRANSFER TRIM, LEVER HANDLE",
  ",A&B_Product,",
  "User, Name",
  ","
]

Here is the example in a flow:

Split Expression Results

So if we want to get the individual items of the split expression we can use combinations of first() skip() last() with the split():

First item in the split collection expression:

first(split(variables('TextString'),'\"'))

Returns:

P001234-LV,

Second item in the split collection expression:

first(skip(split(variables('TextString'),'\"'),1))

Returns:

PER TRANSFER TRIM, LEVER HANDLE

Third item in the split collection expression:

first(skip(split(variables('TextString'),'\"'),2))

 Returns:

,A&B_Product,

Fourth item in the split collection expression:

first(skip(split(variables('TextString'),'\"'),3))

 Returns:

User, Name

There is not really a need to get the last item of the split collection because it is just the ending comma ',' by itself.

Here are the string examples in a flow:

Split Sections

So I wanted to show how the string can be split into sections. This will help organize what commas need to be replaced and with what.

Think of how each individual section needs to be changed with character replacement and then we can join them all together.

Here is a good reference for what expressions are used:

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#...

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#... 

[Chriddle]

Assuming, a Compose action named Data contains the text, this expression

concat(
	first(split(outputs('Data'), ',')),
	'+',
	join(take(skip(split(outputs('Data'), ','), 1), 2), ','),
	'+',
	join(take(skip(split(outputs('Data'), ','), 3), 3),','),
	'+'
)

returns

P001234-LV+\"PER TRANSFER TRIM, LEVER HANDLE\"+A&B_Product,\"User, Name\"+

[Chriddle]

Same idea 😀

[wskinnermctc]

Yeah @Chriddle I was trying to prepare for the amount of commas being variable.

The name Per Transfer Trim, Lever Handle looks like some kind of item title that might not always have a comma. Similar to the product name as well as user name.

Even so, when I initially tried to do exact commas I was having a problem making it. I didn't think of what you made. You gave exactly what OP asked in an easy to follow expression. 

[sayyedkohler]

I am really sorry, my data was a bit incorrect, can you check my question again?
I want to ignore those commas only which come under this scope: \" ,,  \"
Here, this scope(\"   \") may contain multiple commas.

Suppose, the below data. here I only want to replace green colored commas with any new data.
abcd1234, \" ab,cd, \", userdata, \"zx,yz,\", Name, Region, \"zx,yz,\"

@wskinnermctc  Thank you very much for taking the time for me and I really appreciate your efforts 😊😊 
Actually I am trying to convert csv into excel data without using any premium connectors, but some column contains comma, so I am stuck there

[Chriddle]

There are countless tutorials on how to achieve this 😉

However, I think with buildin PA functions you won't succeed. Your whole idea does not work.

Check the  (paid) Encodian action, use PA Desktop, or write your own connector.