Today we are going to go over the different methods to easily remove parentheses (Brackets) in Excel.
While reading documents, articles, or write ups we consciously or unconsciously come across parentheses.
They are the round brackets ( ) mostly used to separate a group of words from the rest of the sentence.
Parentheses as a symbol, have different use cases in various fields.
In mathematics, it is used to direct us on how to order our operations as well as to signify multiplication.
In English languages, (according to the Cambridge dictionary) it is put around a word, phrase, or sentence in a piece of writing to show that what is inside them should be considered as separate from the main part.
Interestingly, Parentheses also has its unique function in Excel.
In excel, whenever we import data from other sources, it sometimes comes in formatted with parentheses around the data.
This could pose a challenge if you are not conversant with excel.
Imagine manually editing numerous data across hundreds of rows and columns.
In fact, because parentheses are an important component of formulas, extra caution needs to be put in place so that the formulas do not get rendered inoperative.
Two scenarios come to mind when dealing with a dataset that has incorporated parentheses.
- Firstly, we may just need to remove the parentheses symbol from our record
- Secondly, we may want to remove the Parentheses and its content from our record.
Whichever the case, we can use excel to carry out our task.
In this tutorial, I will show you how to remove parentheses (Brackets) in Excel using the following methods:
- Removing the parentheses symbol from our record
- Using the Find and Replace Command
- Using VBA
- Using the Substitute Function
- Secondly, we may want to remove the Parentheses and its content from our record.
- Using the Find and Replace Command
- Using the LEFT and FIND function
Before we proceed, it is important that we define key terms that may be used in the course of this learning
- Columns – This is a vertical grid-line in excel
- Rows – This is a horizontal grid-line in excel
- Function: This is a pre-established formula that performs calculations by using specific values, called arguments, in a particular order E.g., AVERAGE, COUNT, COUNTIF
- VBA – This is an acronym for Visual Basic Application. It is an event-driven programming language
- Sub: This is short for Subroutine. A subroutine is primarily a piece of code that executes a specific task described in the code
- Dim: This is an acronym for Declare in Memory. It is used to declare a variable to a specific data type
- Set – This is a statement that is used to assign a value to an object
- Wildcard – wildcard is a special character that lets you accomplish ‘fuzzy’ matching of text. For example, the asterisk wildcard (*) can be used to represent zero or more characters in a string.
For this tutorial, we will be using the table shown below to understand how to carry out this task.
This table shows the names and ages of 10 participants of a survey. Our task will be to demonstrate how to:
- Remove only the parentheses symbol
- Remove the parentheses and its content.
S/N | Name |
1 | Ekas Osubor (30) |
2 | Chinedu Enebeli (32) |
3 | Nathan Olisedumbiri (0.4) |
4 | Aki Antonia ( |
5 | Nduka Ogo (35) |
6 | Henry Joe (15) |
7 | Ene Odioko (25) |
8 | Okwudi Isaac (20) |
9 | Chioma Igabari (16) |
10 | Blessing Aziba (19) |
Here’s How to Remove Parentheses (Brackets) in Excel:
Removing only the Parentheses Symbol
Here we will explain how to remove the parentheses symbol from a dataset using the three methods below:
- Using the Find and Replace Command
- Using VBA
- Using the Substitute Function
Using the Find and Replace Command Method
The ‘Find and Replace’ command happens to be one of the simplest methods used to remove characters (and the parentheses symbol is not an exemption).
This command simply identifies all parentheses and replaces them with another character or removes them from our dataset.
It is best used when working on the original cells.
The steps below outline what needs to be done to achieve this;
Step 1: Navigate to the Home tab in excel
Step 2: Click on the Find & Select dropdown Button
Step 3: A drop-down appears
Step 4: Click on the Replace option (or use CTRL + H on your keyboard)
A Find and Replace dialogue box pops up
Step 5: In the ‘Find what’ field, type the symbol ‘(‘
Step 6: Leave the ‘Replace’ Filed blank. This is to ensure that excel deletes the ‘(‘
Note:
Click on the option box to further define your search parameters
Select
- Within>Sheet: if you will like to search for the data in a worksheet
- Within>Workbook: if you will like to search for the data in a worksheet
- Search: This allows you to define if your search will be done by rows or by columns
- Match case – This is used If you desire to search for case sensitive data
- Match entire cell contents – This is checked if you wish to search for cells that contain exactly the characters in your search criteria
Step 7: Click on the ‘Replace All’ button if you wish to replace all instances of the ‘(‘ symbol. If you want to only remove the ‘(‘ from the selected range then press the ‘Replace’ button.
Step 8: A notification hinting you at the number of replacements pops up
Step 9: Click OK
You will discover at this point that all the opening parentheses in the selected range/sheet have been removed.
Now we need to remove the closing parentheses
Step 10: Navigate to the Home tab in excel
Step 11: Click on the Find & Select dropdown Button
Step 12: A drop-down appears
Step 13: Click on the Replace option (or use CTRL + H on your keyboard)
A Find and Replace dialogue box pops up
Step 14: In the ‘Find what’ field, type the symbol ‘(‘
Step 15: Leave the ‘Replace’ Filed blank. This is to ensure that excel deletes the ‘)‘
Step 16: Click on the ‘Replace All’ button
Step 17: A notification hinting you at the number of replacements pops up
Step 18: Click OK
You will discover at this point that all the Closing parentheses in the selected range/sheet have been removed.
Using the Substitute Function Method
This function is used to substitute a character or set of characters with another.
Its syntax is as shown below:
SUBSTITUTE(text, old_text, new_text,[number instance]
Where,
- The Text argument – refers to the string or reference cell which contains the string you wish to work with. This argument is required
- The old_text argument – refers to the character or set of characters that you intend to replace. This argument is required
- The new_text argument – refers to the character or set of characters that you wish to replace the old_text with. This argument is required
- The number_instance argument – refers to the number of instances of the old_text that you want to replace. This argument is optional.
To use the SUBSTITUTE function, the steps shown below should be followed:
Step 1: Click the cell where you wish to display your result (In our example, Cell C2)
Step 2: Type in the equal to sign (=)
Step 3: Type the function SUBSTITUTE
Step 4: Input the opening parentheses ‘(‘
Step 5: Select the text or reference cell which contains the string you wish to work with. In our example this is B2
Step 6: type in a comma ‘,’
Step 7: Type the old_text in a double quote. That is the character you wish to have replaced. In our example, this is “(“
Step 8: Type in a comma ‘,’
Step 9: Input the new_text in a double quote (that is the character you wish to substitute in. In our example, this is a null input “”
Step 10: Input the closing parentheses ‘)‘
Step 11: Click ENTER
Step 12: Select Cell C2. You will notice a square at the bottom right (i.e. the fill handle)
Step 13: Double click on the fill handle or Drag it down to the last record to copy the formula across the range
You will notice that the opening parentheses have been removed from the dataset displayed in Column C.
To remove the closing parentheses, we will perform the same set of activities with the closing parentheses ‘)’ as the old_text
Step 14: Click the cell where you wish to display your result (In our example Cell D2)
Step 15: Type in the equal to sign (=)
Step 16: Type the function SUBSTITUTE
Step 17: Input the opening parentheses ‘(‘
Step 18: Select the text or reference cell which contains the string you wish to work with. In our example this is C2
Step 19: type in a comma ‘,’
Step 20: Type the old_text in a double quote. That is the character you wish to have replaced. In our example, this is “)“
Step 21: Type in a comma ‘,’
Step 22: Input the new_text in a double quote (that is the character you wish to substitute in. In our example, this is a null input “”
Step 23: Input the closing parentheses ‘)‘
Step 24: Click ENTER
Step 25: Select Cell D2. You will notice a square at the bottom right (i.e. the fill handle)
Step 26: Double click on the fill handle or Drag it down to the last record to copy the formula across the range
You will notice that the closing parentheses have been removed from the dataset displayed in Column D.
Using VBA
VBA allows you to easily automate various activities in Excel.
This is done by writing a program from the backend.
I know this sounds like a programmer’s task, but trust me when I say it’s very easy.
Firstly, we need to be sure that the “Developer” Tab in your excel ribbon is activated. If this isn’t the case, simply
- Go to File
- Click on Options from the excel dialog box
- Click on the Customize Ribbon in the left pane
- Check the Developers option on the right pane
- Click OK.
Now let’s get started with VBA!
Here I will show you have to write a program to add text before and after a string.
Step 1: Click on the Developer tab
Step 2: Select the Visual Basic Icon on the Left
The VBA Window opens.
You will notice that while outlining the steps for removing only the parentheses symbols for both the ‘Substitute function’ and ‘Find and Replace’ methods, we had to remove the opening and closing parentheses one at a time.
However, with VBA you can write a code to remove both symbols.
Now let’s get started!
Step 1: Click Insert and Select Module
A blank Module window is created.
Step 2: Copy and paste the code below
Sub Removing_Only_the_Parentheses()
Dim ws As Worksheet
Dim myrange As Range
Dim result As Range
Set ws = Application.ActiveSheet
Set myrange = Application.Selection
For Each cell In myrange
cell.Value = Replace(cell.Value, "(", "")
cell.Value = Replace(cell.Value, ")", "")
Next
End Sub
Let me quickly explain what we are trying to achieve with this code
This is a ‘For Loop’ program.
During the code execution, an analysis of all cells within the range selection is carried out.
For every cell analyzed, a ‘(’ and ‘)’ are identified and removed from the dataset.
If there are no ‘(’ and ‘)’ discovered again, the program execution is ended.
Step 3: Close your VBA window
Step 4: Navigate to the worksheet and select the range of cells containing the text you wish to convert (In our example, this is B2:B11)
Step 5: Click on the Developer tab
Step 6: Select the Macros Icon on the Left
Step 7: A Micros dialog box pops out
Step 8: In the “Macros name” field, Select the Subroutine name you just created on your VBA (In our example, the subroutine is (‘Removing_Only_the_Parentheses’)
Step 11: Click on the Run button.
You will notice that the parentheses in the dataset is erased.
NOTE: Ensure that your data is saved elsewhere because this method when performed, permanently changes the dataset
Removing the Parentheses and its Content Using the Find and Replace Command Method
We have been able to illustrate how to remove the parentheses symbol from a dataset.
However, there might be scenarios where we may want to totally remove the parentheses symbol alongside its content.
Over the next two methods I will show you how this can be done;
- Using the Find and Replace Command
- Using FIND and LEFT
Using the Find and Replace Command Method
The Find and Replace method can also be used to remove parentheses, as well as its entire content from a dataset.
To achieve this, we need to use a wildcard that denotes the text inside the parentheses symbol. Excel has three known wildcards namely:
- Question mark (?) – used to find any single character
- Asterisk (*) – Used to find zero or more characters
- Tilde (~)
For the task given above, we will be using the (*) wildcard because as you may notice the content within the parentheses vary in the number of characters.
The steps below outline what need to be done to achieve this;
Step 1: Navigate to the Home tab in excel
Step 2: Click on the Find & Select dropdown Button
Step 3: A drop-down appears
Step 4: Click on the Replace option (or use CTRL + H on your keyboard)
A Find and Replace dialogue box pops up
Step 5: In the ‘Find what’ field, type the wildcard ‘(*)‘
Step 6: Leave the ‘Replace’ Filed blank. This is to ensure that excel deletes the parentheses and its content
Note:
Click on the option box to further define your search parameters
Select
- Within>Sheet: if you will like to search for the data in a worksheet
- Within>Workbook: if you will like to search for the data in a worksheet
- Search: This allows you to define if your search will be done by rows or by columns
- Match case – This is used If you desire to search for case sensitive data
- Match entire cell contents – This is checked if you wish to search for cells that contain exactly the characters in your search criteria
Step 7: Click on the ‘Replace All’
Step 8: A notification hinting you at the number of replacements pops up
Step 9: Click OK
You will discover at this point that all the parentheses and its content in the selected range/sheet have been removed.
Using the LEFT and LEFT Function
Here, we will combine two functions to remove parentheses and its content in excel.
They are the LEFT function and the FIND function.
The LEFT function returns the first character or characters in a text string from the left based on the number of characters you specify.
The FIND function is used to find the position of a substring in a string.
This is a combination of two functions.
- The LEFT function – This function returns the first character or characters in a text string from the left based on the number specified
- The FIND function – This function is used to find the position of a substring in a string
The LEFT FIND Function combination has a syntax of:
=LEFT (text, FIND (find_text, within_text, [start_num])
Where
- The Find_text argument – refers to the string that you want to match. The find_text and text arguments in this function are the same.
- The Within_text argument – refers to the text that you want to search in
- The start_num argument – refers to the position in within_text where you want to start your search from. This is an optional parameter
To use the LEFT and FIND function, the steps shown below should be followed:
Step 1: Click the cell where you wish to display your result (In our example, Cell C2)
Step 2: Type in the equal to sign (=)
Step 3: Type the function LEFT
Step 4: Input an opening parentheses ‘(‘
Step 5: Select the text or reference cell which contains the string you wish to work with. In our example this is B2
Step 6: type in a comma ‘,’
Step 7: Type the function FIND
Step 8: Input an opening parentheses ‘(‘
Step 9: Input the find_text value in a double quote. In our example, this is “)”
Step 10: Select the within_text (that is the reference cell). In our example this is B2
Step 11: Input your start number. This is the position in within_text where you want to start your search from. In our example we will will use a start_number of – 1 so that every character after the “(“ is removed.
=LEFT(B2,FIND(“(“,B2,1)-1)
Step 12: Press the Enter key on your keyboard
Step 13: Select Cell C2. You will notice a square at the bottom right (i.e. the fill handle)
Step 14: Double click on the fill handle or Drag it down to the last record to copy the formula across the range
You will notice that the parentheses and its content have been removed from the dataset displayed in Column C.
Conclusion
In this tutorial, we have learned how to Remove Parentheses (Brackets) in Excel as well as how to:
- Remove only the parentheses symbol and
- Remove the parentheses and their content
Hopefully this tutorial helped you simplify the process and answered any questions you may have had!