Find all instances of parentheses in a call and replace with an S

[Peptide]

Hello - Looking for a FORMULA that can find all instances of parentheses and replace with an S. There can be up to 50 parentheses in a cell if that matter.

A1 = H2N-(dA)(dVaaaa)(Dava)(dC/CyMal)-OH
A2 - H2N-SSSS-OH

Possible?

 

[Peptide]

Hi Rick,
No Mac and 1,000 lines would be max. I can hear your mind churning??

 

[Rick Rothstein]

Hi Rick,
No Mac and 1,000 lines would be max. I can hear your mind churning??

Then the code I gave you should cause no problems across any of your Excel versions (you are well below any Excel version limit thresholds for the functions I used that could possibly cause a problem).

 

[Peptide]

Rick - thanks again. I am not sure why we had the issue but I sent out a previous file with DBA's and we could not figure out why some users could not use (no mac's). I am going to hope that Marcelo (or someone else) may have a solution.

Thx Rick.

 

[Rick Rothstein]

Rick - thanks again. I am not sure why we had the issue but I sent out a previous file with DBA's and we could not figure out why some users could not use (no mac's). I am going to hope that Marcelo (or someone else) may have a solution.

The only formula solution I can think of uses multiple helper cells and a somewhat longish function to combine the results. I would note, though, that formulas are not my strong suit, so it is possible I am overlooking something. To help Marcelo and others, is there a maximum number of sets of parentheses that could appear in a single cell? If so, what is that number?

 

[Marcelo Branco]

To help Marcelo and others, is there a maximum number of sets of parentheses that could appear in a single cell? If so, what is that number?

Trying to create a formula - hard; if more than one text outside the parentheses - very hard! Is it possible?

Any pattern in the text outside the parentheses? Always 3 letters?

I have to leave now. I'll take a look later. Still thinking...

M.