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

Peptide

Board Regular
Joined
Apr 12, 2016
Messages
220
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

Board Regular
Joined
Apr 12, 2016
Messages
220
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
Maybe...

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

M.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,415
Office Version
2010
Platform
Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,119
Most importantly, you should have been explicit about A2 which must be taken as the expected result...
 

Peptide

Board Regular
Joined
Apr 12, 2016
Messages
220
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,415
Office Version
2010
Platform
Windows
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)...
Code:
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

Board Regular
Joined
Apr 12, 2016
Messages
220
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,415
Office Version
2010
Platform
Windows
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?
 

Forum statistics

Threads
1,077,984
Messages
5,337,529
Members
399,154
Latest member
gavlink

Some videos you may like

This Week's Hot Topics

Top