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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl63, width: 280"]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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 619"]
<tbody>[TR]
[TD="class: xl65, width: 619"]["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"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]Agriculturist Businessperson, Builder, 1990-95 & 95-97 Corporator, Parola Nagarpalika (Two Terms), , Agriculture and defence affairs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 619"]
<tbody>[TR]
[TD="class: xl65, width: 619"]["Agriculturist Businessperson", "Builder", "1990-95 & 95-97 Corporator", "Parola Nagarpalika (Two Terms)", "Agriculture and defence affairs"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 619"]
<tbody>[TR]
[TD="class: xl65, width: 619"]["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"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]President of Istanbul[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 619"]
<tbody>[TR]
[TD="class: xl65, width: 619"]["President of Istanbul"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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!!