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?

 

[Aladin Akyurek]

Is this what you need?

=SUBSTITUTE(SUBSTITUTE(A2,"(","S"),")","S")

 

[Peptide]

Thx Aladin,

I tried that an it gives me

H2N-SdASSdVSSdVSSdESSdC/Cy5MalS-OH Looking for A2 to = H2N-SSSS-OH thoughts?

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe...

=LEFT(A2,SEARCH("-",A2))&REPT("S",LEN(A2)-LEN(SUBSTITUTE(A2,"(","")))&"-"&TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",100)),100))

M.

 

[Rick Rothstein]

Does it have to be done with a formula? You can do it easily enough using Excel's Replace dialog box. First, assuming you want to leave the original intact, copy the contents of cell A1 to cell A2. Next, with cell A2 selected, press CTRL+H to bring up the Replace dialog box. Put this in the "Find what" field...

(*)

Next, put an upper case S in the "Replace with" field. Next, click the "Options>>" button an make sure the "Match entire cell contents" checkbox is not checked, then click the "Replace" button (not the "Replace All" button). Finish by clicking the "Close" button. If you have more than one cell to process, then select all of them and follow the above procedure, but finish by clicking the "Replace All" button instead.

 

[Aladin Akyurek]

Most importantly, you should have been explicit about A2 which must be taken as the expected result...

 

[Peptide]

Marcelo,
This worked unbelievable. I am going to press my luck as I made a mistake by not stating that there are occasion when A1 can have a letter that is not in ( ).

Similar Example
A1 = H2N-(dA)ABC(dVaaaa)(Dava)(dC/CyMal)-OH
A2 = H2N-SSABCSS-OH

It this possible as well?

 

[Rick Rothstein]

Marcelo,
This worked unbelievable. I am going to press my luck as I made a mistake by not stating that there are occasion when A1 can have a letter that is not in ( ).

That one change makes the approach Marcelo used unusable and may well make a straight formula solution impossible. While other try to decide if I am wrong and attempt a straight formula solution, let me propose a UDF (user defined function) for you to try (note, I named the function after you)...

Function Peptide(ByVal S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(Replace(S, ")", "("), "(")
  For X = 1 To UBound(Parts) Step 2
    Parts(X) = "S"
  Next
  Peptide = Join(Parts, "")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Peptide just like it was a built-in Excel function. For example,

=Peptide(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peptide]

Rick,
Thanks so much for the UDF advice and I do use them on a lot of work. The issue we find is that this file is going to be used by many people on many different excel versions and our experience has been that we have issues with it not working across all of them correctly. I am hoping (actually praying..) that maybe Marcello is able to help...

Thanks in advance!

 

[Rick Rothstein]

The issue we find is that this file is going to be used by many people on many different excel versions and our experience has been that we have issues with it not working across all of them correctly.

What is the maximum number of rows you might actually ever have data in?

Will any of your user ever be using a Mac?