Update string using Regex

[kc20]

Hi there

I have a data set that i would like check for commas ',' within string that is enclosed within double quote, if there is a comma then look for the index of last comma, add two to the index value, look for space after that index value +2, we will replace that space with "|"

here are few examples, focusing on "MMA360L-4734, MMA360L-4724, MMA360L-4811,MMA360L-4702 FirstName LastName" part of the string will be converted into "MMA360L-4734, MMA360L-4724, MMA360L-4811,MMA360L-4702"|"FirstName LastName"

"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734 MMA360L-4724 MMA360L-4811MMA360L-4702"|"FirstName LastName"|""|""|"8"|""|""||
"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734, MMA360L-4724, MMA360L-4811,MMA360L-4702 FirstName LastName"|""|""|"8"|""|""||
"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734, MMA360L-4724, MMA360L-4811, MMA360L-4702 FirstName LastName"|""|""|"8"|""|""||
"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734,MMA360L-4724,MMA360L-4811,MMA360L-4702 FirstName LastName"|""|""|"8"|""|""||

Output should be as following:
"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734 MMA360L-4724 MMA360L-4811MMA360L-4702"|"FirstName LastName"|""|""|"8"|""|""||
"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734, MMA360L-4724, MMA360L-4811,MMA360L-4702"|"FirstName LastName"|""|""|"8"|""|""||
"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734, MMA360L-4724, MMA360L-4811, MMA360L-4702"|"FirstName LastName"|""|""|"8"|""|""||
"Dec 2024 TRG TCs"|"H:\repos\Tempo-Data\Timesheets\TRG\Dec 2024 TRG TCs.pdf"|"Alteryx table"|"10"|"25"|"12/18/2024"|"MMA360L-4734,MMA360L-4724,MMA360L-4811,MMA360L-4702"|"FirstName LastName"|""|""|"8"|""|""||

[NaiLo · Accepted solution]

So you're basically trying to add a pipe between your last "account number" (e.g. "MMA360L-4702" and [FirstName], is that correct?

I'm not a fan of using the string index to do this, but I've attached an example to show you what it could look like with a formula tool. I've configured the tool to show you each step individually:

1. First, find the index for the last comma in the string by using a combination of FindString and ReverseString ([last comma index])

2. Parse out the string that comes after the last comma ([First String Group])

3. Find the index for the first space in the string you parsed in step 2 ([first space after last comma index])

4. Combine your original string up to and including the last comma with the string from step 2, followed by your pipe delimiter and the rest of your original string ([Output])


My preference is to use Regex Replace since it's much more robust and easier to understand and update. I've added a RegEx Replace configuration to the workflow so you can see what this would look like.