Most of my time working in Excel is finished cleaning data that I get from different sources. One common task that I have to do totally commonly is to remove brackets from the cells in a dataset.
In this blog, I’m going to show you four such methods that you can work depending on the layout of your data place or your private preference. Each method has its pros and cons, and I would list them so that you can decide which method is best suited for you.
In this blog, I am showing you how to remove parenthesis, but you can also use the same methods to remove brackets as well.
- Find and Replace to Remove Parentheses
Find and Replace is one more easy way to find and remove all the parentheses in your excel sheet. Since the parentheses contain an opening bracket and a closing bracket, you need to use Find & Replace twice to remove the two brackets separately.
2. Formulas to Remove Parentheses
If you want more control while removing the parentheses from your excel sheet, you can consider using the formula method. This can be done using the SUBSTITUTE function, which would identify the location of the opening and closing brackets and replace them with a null line.
Here is the formula that will do this:
=SUBSTITUTE (SUBSTITUTE (A2,”(“,””),”)”,””)
Enter the up above formula in cell B2 and then apply it to the entire column. The above formula uses two substitute functions. The first SUBSTITUTE function finds the position of the opening parenthesis character (“(“) in the cell and substitutes it with a null string. The result of the first SUBSTITUTE function is then used by the second SUBSTITUTE function to find the closing parenthesis character (“)”) and replace it with a null string.
3. VBA to Remove Parentheses
Since this formula requires a small bit of format, it is more suites for people who need to do this regularly. So you can set up the VBA code once and then run it with a simple keyboard shortcut or a click of a button to remove parentheses from the selected data set. Let me show you how this method works. Below I have the identical data set where I have the names in column A across with the state names in parentheses, and I want to remove these parentheses.
Here are the steps to do this:
1) Select the data set.
2) Than Hold Press ALT + F11 key to open the Visual Basic editor. or also go to the Developer tab and then click on the Visual Basic icon.
3) In the VB Editor, click on the Insert option in the menu and then click on the Module option. This will place a new module for the workbook.
4) Copy and paste the below VBA code into the module code window.
Sub RemoveParentheses()
Dim rng As Range
Dim cell As Range
' Set the current selection as the range to be used
Set rng = Selection
' Loop through each cell in the selection
For Each cell In rng
' Remove parentheses
cell.Value = Replace(cell.Value, "(", "")
cell.Value = Replace(cell.Value, ")", "")
Next cell
End Sub
To run this macro code, point the cursor anywhere inside the code, and then press the F5 Key or click on the run macro icon in the toolbar. As soon as you run the above VBA code, it will go along each cell in the selection, and then replace the opening bracket and closing bracket characters with a null string. Once you have this macro in your workbook, you can reuse it multiple times in that workbook. You can also assign a keyboard shortcut to the macro so you can run easily without opening the VB Editor.
To assign a keyboard shortcut to a macro, follow the below steps:
Click on the Developer tab in the ribbon
Click on the Macros option
Select the macro name for which you want to assign a keyboard shortcut
Click on the Options button
Specify the shortcut while this is not the fastest way to remove parentheses from Excel, once you have the code in place, you can reuse it multiple times in the workbook that has the code.