Insert square brackets and double quotes to a string

[spuri5]

Hello Alteryx Community,

My current output is looking like this in JSON format as shown below:

Current Output 1
"Name": "AAA","BBB","CCC"

Current Output 2
"Name":""

Current Output 3
"Name":"HHH"

The expected output should be like as shown below:

Expected Output1
"Name": ["AAA","BBB","CCC"]

Expected Output2
"Name": [""]

Expected Output3
"Name": ["HHH"]

Thank you!

[CathyS_Slalom]

Hi @spuri5, If it always start with "Name": you can use the formula below

[spuri5]

Hello Cathy, 

Thank you for the reply,

But the Name string value is not always 3 digits.

Please advise! 

[CathyS_Slalom]

Hi @spuri5 , if that's the case, please use the formula below (locate the : to get the dynamic name length):

ReplaceFirst([Old], left([Old],FindString([Old], ":")+1), left([Old],FindString([Old], ":"))+ ":[") + "]" 

[binuacs]

@spuri5 Another way of dong this with the RegexReplace formula

[spuri5]

Hi Cathy,

With the below formula, the output I am getting:

Current Output 1

"Name": "AAA,BBB,CCC]",

"Name":"]",

"Name":"HHH]",

But the expected output should be as shown below:

Expected Output1
"Name": ["AAA","BBB","CCC"],

"Name": [""],

"Name": ["HHH"],

[spuri5]

Hi @binuacs ,

With the below formula, the output I am getting: 

Current Output 1

"Name": "AAA,BBB,CCC:[AAA,BBB,CCC]",

"Name":":[]",

"Name":"HHH:[HHH]",

But the expected output should be as shown below:

Expected Output1

"Name": ["AAA","BBB","CCC"],

"Name": [""],

"Name": ["HHH"],

[CathyS_Slalom]

Hi @spuri5 If your input data is matching the pattern that under Old column, and you use the function in the picture, you should be able to see the expected output as shown under the "New" column. Could you make sure that you applied this new formula after your previous/current output that you listed below?

[binuacs]

@spuri5 as @CathyS_Slalom said the regex formula works based on your given input file and its works for me. Can you provide the input file which you are trying with the formula? output as per your given input

[spuri5]

Hi @CathyS_Slalom and @binuacs ,

Sorry for the confusion, Please find attached the actual sample Input file.

Expected output as shown below: