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.
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"
Peptide = Join(Parts, "")
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,
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.
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...
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.