Voluminous sets of data are often characterized by petty problems like unnecessary parentheses.
You may face this problem within Excel when you’ve imported data from an external source when you have copied and pasted data in its source formatting, or when you otherwise want to filter out parentheses from a given set of data to use it for different purposes.
How to Remove Parentheses in Excel?
Parentheses can be removed from Excel through different methods. However not every method might prove to be effective as it might render the underlying formulas inoperative.
Below, we have pulled together the two easiest and fastest ways to remove parentheses in Excel.
1. Using the Find and Replace Function to Remove Parentheses in Excel
The ‘Find and Replace’ function makes the easiest method to remove parentheses from a given dataset in Excel. Follow these simple steps to filter out parentheses from your data in Excel using the ‘Find and Replace’ function.
Step 1:
Select the cell range.
Step 2:
Go to the Home tab > Editing > Find & Select > Replace.
Tip: You can alternatively reach out for the ‘Find and Replace’ function by using the shortcut keys ‘Ctrl+H’.
This would open up the Find and Replace dialogue box.
Step 3:
In the ‘Find what’ option, type in the opening parenthesis only i.e. ‘(‘. Leave the ‘Replace’ option vacant and press ‘Replace All’.
Doing so, Excel has removed all the opening parentheses from the selected cell range.
Step 4:
Again open up the ‘Find and Replace’ function and this time populate the ‘Find what’ option with closing parenthesis i.e. ‘)’. Again leave the ‘Replace’ option vacant and press ‘Replace All’.
Excel has now removed both the opening and closing parentheses from the selected cell range.
2. Using the SUBSTITUTE Function to Remove Parentheses in Excel
The second easiest technique to remove parentheses in Excel is about employing the SUBSTITUTE function of Excel. This function works on the mechanism of the ‘Find and Replace’ Function.
However, what differentiates this function from the contemporary ‘Find and Replace function is that you can simultaneously have the values with and without parentheses within the same Excel sheet unlike the ‘Find and Replace’ function where the original values are replaced, and only the replaced values continue to appear.
Syntax:
= SUBSTITUTE (text, old_text, new_text)
Text refers to the text or cell reference that contains the value to be replaced.
Old_text refers to the original value that needs to be replaced.
New_text refers to the replaced value.
To remove parentheses from a given data set in Excel using the SUBSTITUTE function, follow these simple steps.
Step 1:
Select a cell and type in the formula for SUBSTITUTE function as follows.
=SUBSTITUTE(B2,”(“,””)
- B2 represents the cell reference i.e. the cell where the parenthesis to be removed exists.
- “(“ represents the old text to be replaced i.e. the opening parenthesis.
- “” represents the replaced value i.e. no value, as we only want the parenthesis to be removed and not to be substituted with another value.
Press enter and see how excel reproduces the value of B2 without the opening parenthesis.
Drag the Fill Handle to have the same formula applied to all the required cells.
Tip: Instead of typing, you can readily choose the SUBSTITUTE Formula from the Formulas Tab > Function Library > Text > Substitute as follows.
Step 2:
Select the first cell of the Column / Row where you want the values post parenthesis-removal to be populated. Type in the formula for the SUBSTITUTE function for closing parenthesis as follows.
=SUBSTITUTE (C2,”)”,””)
Press enter to see excel reproduce the values of C2 without closing parenthesis, which is the value of B2 with both the opening and closing parenthesis removed.
Drag the Fill Handle up to the required cell.
Must note how you still have the values with parenthesis appearing in Column B and values without parenthesis appearing in Column D.
Pro Tip: You can copy the values appearing in Column D and special paste them as simple text in a different column to yield values without including an underlying operative formula.
Bottom Line:
The foregoing techniques do not make an exhaustive list of techniques that can be used to remove parentheses in Excel.
However, employing them can help you yield fast and fool-proof results with little effort.
Suggested Tutorial: Fill Handle in Excel
