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

Peptide

Board Regular
Joined
Apr 12, 2016
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Thx Aladin,

I tried that an it gives me [TABLE="width: 301"]
<tbody>[TR]
[TD="width: 301"]H2N-SdASSdVSSdVSSdESSdC/Cy5MalS-OH

Looking for A2 to = H2N-SSSS-OH

thoughts?[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Maybe...

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

M.
 
Upvote 0
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.
 
Upvote 0
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?



 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top