When working in Excel, data cleaning takes up a significant portion of my time.
Dealing with data from various sources often means removing parentheses (or brackets) from cells in a dataset. Lucky for you, you can accomplish this through multiple ways in Microsoft Excel, each with its pros and cons.
Note: In this blog post, I’ll show you how to remove parentheses in Excel (). Also, you can apply the same methods to remove brackets in Excel .
Also Read: How to Convert a Word Document to Excel
Remove Parentheses in Excel (4 Methods)
This article teaches you four different ways to remove parentheses in Excel (with examples and pictures).
Our methods of removing parentheses in Excel will allow you to choose the best-suited method based on your data structure or personal preference.
I’ll use the dataset below to show how each method works.
Method 1: Remove Parentheses in Excel Using Flash Fill
One tool that impresses me during basic data clean-ups is Flash Fill. (This powerful feature in Excel makes removing parentheses or brackets a breeze).
To use the Flash Fill tool, manually enter the desired result in an adjacent cell.
As you run Flash Fill, it cleverly recognizes the pattern you’ve specified and applies it across the entire column.
Here’s an example of how it works:
Consider the following dataset: names followed by state names wrapped in parenthesis.
Here’s how to delete parentheses in Excel:
- Enter the expected result, such as “John Campbell, New York,” in cell B2, adjacent to the dataset.
- Select cell B3.
- Press and hold the Ctrl key and hit the E key to run Flash Fill.
When Flash Fill finishes its work, the entire column transforms, with parentheses and brackets removed from the text.
Alternatively, you can access Flash Fill through the Home tab. Within the Editing group, click the Fill icon and select the Flash Fill option.
Note: Flash Fill’s pattern recognition might not always be accurate. In case of incorrect pattern identification, try providing two or three examples of the desired result and rerunning Flash Fill.
Now, let’s weigh the pros and cons of Flash Fill for removing parentheses:
|Quick and effortless operation.||Double-check the results, as Flash Fill occasionally identifies the wrong pattern.|
|Initially, entering the desired result in one or two cells is required.|
|The obtained results are static values, necessitating repeating the steps if the original data changes.|
Also Read: How to Turn On AutoSave in Word and Excel
Method 2: Remove Parentheses in Excel with Find and Replace
Another straightforward technique for removing parentheses in Excel is Find and Replace. While it might not be as fast as Flash Fill, it’s still reliable. Follow these steps to master it:
- Select the dataset in Excel Workbook from which you want to remove the parentheses.
- Head to the Home tab (if not already selected).
- Click Find & Select > Replace in the Editing group. It will launch the Find and Replace dialog box.
- Enter “(” in the Find what field.
- Click on Replace All to eliminate all instances of “(” in the dataset.
- Repeat the process by entering “)” in the ‘Find what‘ field and replacing all instances of “).”
It is a two-step process in which you delete the starting and ending brackets (parentheses).
Tip: Press the keyboard shortcut Control + H to access the Find and Replace dialog box. Hold the Control key and press the H key.
Now, let’s weigh the pros and cons of the Find and Replace method for removing parentheses in Excel:
|It provides the desired outcome effectively.||No need to add another column; Find and Replace can be applied directly to your original data column.|
|Removes all instances of opening and closing brackets without letting you specify which ones to remove.|
Method 3: Use Formulas to Remove Parentheses in Excel
Consider Excel formulas if you want more control while removing parenthesis from your dataset.
The SUBSTITUTE function comes to your rescue, allowing you to identify the positions of opening and closing brackets and replace them with a null string. Follow the steps below.
- Assume you have a dataset where you wish to eliminate parentheses from state names.
- Employ the following formula in cell B2 and apply it to the entire column (copy the formula cell or use the Fill Handle to drag it down):
The above formula employs two SUBSTITUTE functions. The first SUBSTITUTE function locates the opening parenthesis character (“(“) in the cell and replaces it with a null string.
The result of this initial substitution is passed to the second SUBSTITUTE function, which locates the closing parenthesis character (“)”) and replaces it with a null string.
If you don’t require the original data, convert the formula values in column B into static values and remove the original data.
Now, let’s evaluate the pros and cons of the formula method for removing parentheses/brackets in Excel:
|Automatically update your output if the original data changes.||Provide the results in a separate column, preserving the original dataset.|
|It requires knowledge of the syntax of the SUBSTITUTE function.|
Method 4: Use VBA to Remove Parentheses in Excel
VBA offers a powerful solution for someone to remove parentheses in Excel daily. Although it requires some initial setup, it becomes a time-saving ally once configured.
By setting up a VBA code, you can swiftly remove parentheses from selected datasets using a keyboard shortcut or a simple button click.
Let’s explore how this method works.
1. Select the dataset requiring parentheses removal.
2. Press the ALT + F11 key to open the Visual Basic Editor. Or, select the Developer tab and click Visual Basic on the far left.
3. In the VB Editor, click the Insert tab and select Module. It inserts a new module into the workbook.
4. Now, copy and paste the following VBA code into the module code window:
'Code developed by Aamir Rehman from https://www.techlogicalinc.com Sub RemoveParentheses() Dim cell As Range Dim originalValue As String Dim newValue As String ' Loop through each selected cell For Each cell In Selection originalValue = cell.Value newValue = "" ' Remove parentheses from the original value For i = 1 To Len(originalValue) If Mid(originalValue, i, 1) <> "(" And Mid(originalValue, i, 1) <> ")" Then newValue = newValue & Mid(originalValue, i, 1) End If Next i ' Assign the new value to the cell cell.Value = newValue Next cell MsgBox "Parentheses removed successfully!" End Sub
5. To execute the macro code, place the cursor within the code and press the F5 key or select the run macro icon in the toolbar.
Running the above VBA code will remove the opening and closing parentheses/brackets from the selected cells, and a message box will appear confirming the completion.
It replaces them with a null string. You can reuse this macro after saving it to your workbook.
Additionally, you can assign a keyboard shortcut or add a macro icon to the Quick Access Toolbar for effortless access.
To add a keyboard shortcut to a macro, do the following:
- Click the Developer tab in the Excel ribbon and select Macros.
- Choose the macro name to assign a keyboard shortcut.
- Select the Options button.
- Specify the desired shortcut.
While this is not the quickest way to remove the parenthesis from Excel, once the code is in place, you can reuse it in the worksheet that includes the code numerous times.
Caution: VBA macro code changes are irreversible. Before running the code, make a backup copy of your data.
Now, consider the pros and cons of using VBA to remove parentheses in Excel:
|Once the macro code is in place, you can reuse it several times.||Requires additional time to set up the VBA code in the backend.|
|VBA allows you to set a keyboard shortcut to macro or add a macro icon to the Quick Access Toolbar for quick execution.||Changes made by the VBA code are irreversible.|
Also Read: How to Turn Off AutoSave in Word and Excel
I’ve shown you four unique ways to remove parentheses from Excel cells.
If you have a one-time or occasional requirement, the Flash Fill or Find & Replace method offers quick solutions.
On the other hand, if you frequently encounter this task, the Excel Formula or the VBA method can significantly streamline your workflow.
Choose the method that suits your needs and start efficiently cleaning your data. We hope you find this article valuable and insightful on your Excel journey.
Select the range of cells to remove the parentheses from phone numbers. Then, open the Find and Replace dialog box with the Ctrl+H keyboard shortcut. In the ‘Find what’ field, type the opening parenthesis “(” and leave the ‘Replace with’ field blank. Select Replace All to remove all starting parentheses. Repeat the steps to remove all the closing parentheses.