Most of my time working in Excel is spent cleaning data that I get from different sources.
One common task that I have to do quite often is to remove parentheses (or brackets) from the cells in a dataset.
As with almost everything else in Excel, there are multiple ways you can remove the parenthesis from cells.
In this tutorial, I’m going to show you four such methods that you can use depending on the structure of your data set or your personal 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.
Note: In this article, I am showing you how to remove parentheses (), but you can use the same methods to remove brackets  as well.
Using Flash Fill to Remove Parentheses
To use Flash Fill, you first need to manually enter the result for one of the cells in the adjacent cell.
When you enter the result for one of the cells in the adjacent cell and then use Flash Fill, it will identify the pattern (using the result that you have specified manually), and apply the same for the entire column.
Let me show you how it works with an example.
Below is a data set where I have the names followed by the state name. The state name is in the parentheses that I want to remove.
Here are the steps to do this:
- In cell B2, which is in the column adjacent to our data set, enter the expected result – Richard Hanson, Texas
- Select cell B3
- Hold the Control key and press the E key (this is the keyboard shortcut to run Flash Fill)
As soon as you run Flash Fill, you will notice that the entire column gets filled, and the parentheses/brackets have been removed from the text.
You can also run Flash Fill by going to the Home tab, then clicking on the Fill icon (in the Editing group), and then clicking on the Flash Fill icon.
Note: Since Flash Fill works by identifying the pattern in the result you have provided, there is a possibility that it may identify an incorrect pattern and give you the wrong result. If that happens, try again by giving it two or three examples of the result you want and then running the Flash Fill.
Below are the pros and cons of this method to remove parentheses:
|Quick and Easy||Flash Fill can identify the wrong pattern, so you need to double-check the results.|
|You need to manually enter the result in one or two cells before using Flash Fill|
|The results you get are static values, so if your original data changes, you need to repeat the steps again.|
Find and Replace to Remove Parentheses
Find and Replace is another easy way to find and remove all the parentheses in your data set.
Since the parentheses include an opening bracket and a closing bracket, you need to use Find & Replace twice (to remove the two brackets separately).
Below is an example data set where I have the names followed by the US state name in parentheses, and I want to remove the parentheses only.
Here are the steps to do this:
- Select the data set from which you want to remove the parentheses
- Click the Home tab
- Click on the Find & Select icon in the Editing group
- Click on the Replace option. This will open the Find and Replace dialog box.
- Enter ( in the Find what field.
- Click on Replace All. This will replace all the instances of ( in the dataset.
- Now, enter ) in the ‘Find what’ field.
- Click on Replace All. This will replace all the instances of ) in the dataset.
While this method may not be as quick as using Flash Fill, if you’re comfortable using Find and Replace, it’s fast enough.
Pro Tip: Use the keyboard shortcut Control + H to open the Find and Replace dialog box. To use this, hold the Control key and then press the H key.
Now let’s look at the pros and cons of this method for removing parentheses in Excel.
|Works as expected.||No need to create an additional column. You can use Find and Replace on your original data column itself.|
|It removes all instances of the opening and closing bracket characters. You can not choose which instance of the brackets would be removed.|
Also read: How to Add Parentheses Around Text in Excel
Formulas to Remove Parentheses
If you want more control while removing the parentheses from your data set, you can consider using the formula method.
This can be done using the SUBSTITUTE function, which would identify the position of the opening and closing brackets and replace them with a null string.
Below is a data set where I want to remove the parenthesis from the state names.
Here is the formula that will do this:
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.
If you do not need the original data, you can convert the formula values in column B into static values and then remove the original data.
Now let’s look at the pros and cons of using the formula method for removing parentheses/brackets in Excel.
|If your original data changes, the resulting output would automatically update||You get the result in a separate column|
|You need to know the syntax of the SUBSTITUTE function to be able to use it.|
VBA to Remove Parentheses
And the final way to remove parentheses in Excel is by using a simple VBA formula.
Since this method requires a little bit of setup, it is more suited 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 same data set where I have the names in column A along with the state names in parentheses, and I want to remove these parentheses.
Here are the steps to do this:
- Select the data set.
- Hold the ALT key and press the F11 key to open the Visual Basic editor. Alternatively, you can go to the Developer tab and then click on the Visual Basic icon.
- In the VB Editor, click on the Insert option in the menu and then click on the Module option. This will insert a new module for the workbook.
- Copy and paste the below VBA code into the module code window.
'Code developed by Sumit Bansal from https://trumpexcel.com 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, place the cursor anywhere within 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 through 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.
If you want to use this code in any workbook on your system, you can put it in the Personal Macro Workbook. A VBA code stored in the Personal macro workbook can be used on any Excel workbook on your system.
Caution: Changes done by a VBA macro code cannot be undone. So before you run the code, make sure you have a backup copy of the data.
Now let’s look at the pros and cons of using VBA to remove parentheses/brackets in Excel.
|Once you have the macro code in place, you can reuse it multiple times||Takes more time to set up the VBA code in the back end|
|You can assign a keyboard shortcut to the macro or add the macro icon to the Quick Access Toolbar||The changes done by the VBA code are irreversible.|
So these are four methods you can use to get rid of the parentheses from your cells in Excel.
If you want to do this quickly once or twice, you can use the Flash Fill method or the Find & Replace method. And if you need to do this on a regular basis, you can consider using the VBA method.
I hope you found this article useful.
Other Excel articles you may also like: