Formula to convert text based on commas

[Nola111]

I'm trying to use a formula which would take what's in the column A and convert it to what's in column B. Basically, the entire contents would end up surrounded by square brackets, and anywhere there is a comma, this comma should act as a separator, and finally all phrases separated by commas should be enclosed in parentheses. It would look like this:

	A	B
1	Advocate Agriculturist, 1996 - 2001 Union Councilor, Mangalur Union, District Secretary (A.I.A.D.M.K.) from 2004 : Cuddalore District, Tamil Nadu, Sports, Games and Tourism <tbody> </tbody>	["Advocate Agriculturist, 1996 - 2001 Union Councilor", "Mangalur Union", "District Secretary (A.I.A.D.M.K.) from 2004 : Cuddalore District", "Tamil Nadu", "Sports", "Games and Tourism"] <tbody> </tbody>
2	Agriculturist Businessperson, Builder, 1990-95 & 95-97 Corporator, Parola Nagarpalika (Two Terms), , Agriculture and defence affairs <tbody> </tbody>	["Agriculturist Businessperson", "Builder", "1990-95 & 95-97 Corporator", "Parola Nagarpalika (Two Terms)", "Agriculture and defence affairs"] <tbody> </tbody>
3	Social Worker Agriculturist, Educationist, May, 2014 Elected to 16 th Lok Sabha, , Agriculture and rural development, human resources management, education, sports, international trade and economics, E-governance and strategy, promotion of primary education <tbody> </tbody>	["Social Worker Agriculturist", "Educationist", "May", "2014 Elected to 16 th Lok Sabha", "Agriculture and rural development", "human resources management", "education", "sports", "international trade and economics", "E-governance and strategy", "promotion of primary education"] <tbody> </tbody>
4	President of Istanbul <tbody> </tbody>	["President of Istanbul"] <tbody> </tbody>

<tbody>
</tbody>

Note in row 4, if there are no commas, we would just want the phrase in quotes and brackets.

How would I accomplish this with a formula? Is this done with some combination of Substitute and Trim?

Thanks!

 

[steve the fish]

This will work for your examples:

="["""&SUBSTITUTE(SUBSTITUTE(A1,", ,",","),", ",""", """)&"""]"

 

[Nola111]

This will work for your examples:

="["""&SUBSTITUTE(SUBSTITUTE(A1,", ,",","),", ",""", """)&"""]"

Thanks Steve!

One more question - how would the formula change if I wanted to remove the space after each comma in column B? I tried tinkering with your formula but just kept screwing it up. I'm too much a novice at these formulas, unfortunately.

 

[steve the fish]

If you look at the formula, the last comma in it has a space after it. Remove that space.

 

[Nola111]

If you look at the formula, the last comma in it has a space after it. Remove that space.

A-ha! Thanks so much Steve!!