How to Remove Parentheses in Excel (3 Easy Ways + VBA)

Data speaks to everyone differently. Left alignment, right alignment, font size 11 or 12, format this way, or severely not this way, to delimiter or not to delimiter; that is the problem. But it shouldn't be with Excel. Too often, using the word lightly, data is stored in a way that suits the creator or editor. Their way might be suitable for their tasks but for what you need to get done, hindrance all around.

What would necessitate the removal of any form of brackets in data? A special character when used as part of a text string may result in data discrepancy or as an inhibitor in data processing. Say there's a list of phone numbers with area codes enclosed in round brackets and the system used for contact cannot process characters other than numbers. This will require that the parentheses be ditched for consistency and smooth processing. Let's make this clearer with an example.

The dataset here contains phone numbers that can do without parentheses (for reasons said above). Also, column D is populated with the city name and then the state name bracketed, separated by a comma. We need the data sorted with the city and state in separate columns and before we launch into splitting columns with the comma delimiter, the parentheses need a goodbye. Today's tutorial will give them their farewell.

Do note that while we can say parentheses and define them collectively, they are two separate characters: an opening parenthesis and a closing parenthesis. And that is how we'll get rid of them; one by one. Spoiler says we have a couple of methods that remove them collectively.

Note: Other than the LEFT and FIND functions method (which removes the parentheses and its contents), all other 3 methods remove every instance of the stated parenthesis from a cell's value. You can set the instance number in the SUBSTITUTE function to remove a particular occurrence (as shown later).

Parentheses terminator mode – activated!

Method #1 – Using Find and Replace Feature

Remove parentheses with the Find and Replace feature in Excel. Use Find and Replace as a search bar to locate the parentheses, remove them, and replace them with user-supplied text. As of now, we aim to have the parentheses erased.

Therefore, they will be replaced with an empty text string. Our guess is, like our sample data here, the brackets in your data contain other text between them. Which is why the opening and closing parenthesis will have to be replaced one after the other to retain the contents within.

You have the idea; we can move on to the steps. Below are the details on using the Find and Replace feature to remove parentheses from data in Excel:

• On the worksheet, select the data that contains the parentheses you want to remove.
• In the Home tab, click on the Find & Select button from the Editing Select Replace from the drop menu. You can opt for the keyboard shortcut instead – press the Ctrl + H keys.
• 
• In the Find and Replace dialog box, type the opening parenthesis in the Find what
• Hit the Replace All command button.
• By doing this, you are leaving the Replace with field blank so that the parenthesis can be switched with blank text and consequently, removed.
• 
• Click the OK button of the pop-up detailing the number of replacements made.
• 
• Now you'll be back to the Find and Replace dialog box.
• Empty the Find what field and enter the closing parenthesis this time.
• Select the Replace All
• 
• The pop-up window will appear again with the text replacements.
• Press the Esc key twice to close both dialog boxes.

Returning to the worksheet, the selected data will now be free of the opening and closing parentheses:

Looking at our case example, suppose we want to slash away the parentheses and their contents which will leave us with just the city name instead of city, state. You can still take to Find and Replace for that. Simply enter the opening and closing brackets with an asterisk in between. The asterisk plays as a wildcard, covering any number of characters present between the parentheses. Enter (*) in the Find what box and make the replacements.

There we have removed the state name by replacing the parenthesis, contents included, with blank text:

Notes:

In this sample data above, we only have the city and state name separated by parentheses, without a comma. If the comma was there, in place of "(*)" we would have entered ", (*)" to eliminate the comma and space character along with the brackets and their contents.

The useful bit about using Find and Replace to remove the parentheses-enclosed contents is that the feature will find the position of the brackets automatically in a cell. Unlike the LEFT/RIGHT and FIND functions which remove the enclosed contents from the cell's left or right.

Method #2 – Using SUBSTITUTE Function

Use the SUBSTITUTE function in Excel to remove parentheses from your data. The SUBSTITUTE function is used to replace specific text with other text. That presents us with the perfect opportunity to switch the parentheses in the data out for blank text.

The function itself is straightforward and you don't have to replace the opening and closing parentheses one by one; they can both be replaced using a single formula. Given below is the formula used for removing parentheses in text values with the SUBSTITUTE function:

For the ease of applying a single formula, we have used nested SUBSTITUTE functions. This way, we can add the opening and closing parentheses (enclosed in double quotes) as the values to be replaced by empty text string (denoted by twin double quotes ""). Starting with B2, we can see that the formula has worked fine with numeric text as well as alphabetic text in C2. We have the contact numbers without the area codes enclosed in brackets and the state names only separated by commas.

Copy-pasting the return values to the original dataset and clearing up the redundant columns gives us:

Note: As per our case example, which contains a single instance of parentheses, we have left the instance_num argument empty. If, e.g., there are 2 sets of parentheses in a cell and you want the second set removed, enter the last argument of the SUBSTITUTE function as 2. Not defining this argument will remove both sets from the cell.

Method #3 – Using LEFT & FIND Function

Find out how to remove parentheses and their contents in Excel with the LEFT and FIND functions. Use this option for clearing parentheses and their contained value from a cell. This is the function version of clearing the parentheses part of a cell as we have also seen with the Find and Replace feature.

The enclosed data is on the right side of the cell, and we need the FIND function to locate the opening parenthesis and return the value to the left of it. See the formula ahead which uses the LEFT and FIND functions to erase parentheses along with their contents:

The FIND function searches the position of the opening bracket in C3 with the starting position of the search defined as 1 (i.e. FIND will begin its search from the first character of C3). The LEFT function is set to return the value in C3 that is on the left of the opening parenthesis. -1 in the end ensures that LEFT's return value is without the opening parenthesis itself. Here we have all the city names without the state name and its brackets:

If the bracketed value in your data is on the left, the formula will have to be tweaked to return the value to the right of the closing parenthesis. Here's a sample formula you can go by to remove parentheses and their contents from the left of a cell:

Since we need the value from the right, we incorporate the RIGHT function. The return value is to be the parentheses-free text in C3. The number of characters is set by the LEN and FIND functions where FIND gets the position of the closing bracket as 10 and LEN counts the total characters as 19.

Therefore, RIGHT delivers the 19-10 characters that are on the right side in C3. In column C, after the closing parenthesis, there is a space character which will also be returned as part of the value. To eliminate that, we have fixed a -1 in the end. The outcome of this formula is shown here:

Method #4 – Using VBA Function

Now we'll show you how to remove parentheses using VBA in Excel. VBA programs Office applications into performing tasks. For our case example, we can use a code in VBA to remove just the parentheses in the selected data without altering anything else. The code and the detailed steps for this task to be carried out in Excel are given ahead:

• Select the target data containing the parentheses.
• Use the Visual Basic icon in the Developer tab's Code group to launch the Visual Basic You can also use the Alt + F11 keys to do this.
• 
• In the editor, select the Insert Then select Module from the drop menu.
• 
• You will now have access to a Module window.
• In the Module window, copy-paste the code from below:

• Sub RemoveParentheses()
  Dim ws As Worksheet
  Dim rng As Range
  Dim result As Range
  Set ws = Application.ActiveSheet
  Set rng = Application.Selection
  For Each cell In rng
   cell.Value = Replace(cell.Value, "(", "")
   cell.Value = Replace(cell.Value, ")", "")
  Next
  End Sub

  The code replaces both the opening and closing parentheses in the selected cells with blank text using the Replace function in VBA.

• Hit the Run button in the editor's toolbar (highlighted above) to activate the code.
• The code will do its work in the background, on the worksheet.
• Close the Visual Basic

All the selected data will be free of the parentheses:

Remove parentheses from data? Covered. Remove parentheses with their contents? Covered. Formula, tool, and VBA method? Covered, covered, covered. Yes, we've got you covered with all the easy tricks. But this is just a bunch in a sea of many. So much remains uncovered. We've all got our job cut out. Ready? Tricky? Go!