Remove Parentheses (Brackets) in Excel

by

The parentheses are a pair of symbols that have different meanings in different contexts. In general, parentheses are used to group elements together into an entity.

In the English language, they are used as punctuation to enclose additional information about something.

In accounting, on the other hand, parentheses are used to represent a negative amount in your check register (often to represent a debit amount).

Sometimes, you may find parentheses in telephone numbers to distinguish a country code from the rest of the number.

If you need to get rid of these parentheses in Excel, it’s not so simple, especially if you have a large amount of data.

In this tutorial, we will look at a few ways to remove parentheses from your Excel data.

We will look at two different cases:

  • When you want to remove only the parenthesis symbols
  • When you want to remove the parentheses along with the content inside them

To demonstrate each case, we will use the following dataset:

Names with parenthesis

How to Remove Parentheses Symbols in Excel

There are many ways in which you can remove a particular symbol from data in Excel.

In this tutorial, we will only look at three of the most commonly used methods:

  • Using the Find and Replace Feature
  • Using the SUBSTITUTE Function
  • Using VBA

Using the Find and Replace Feature to Remove Parentheses in Excel

This method is the easiest and the most commonly used method to remove parentheses in Excel.

Use this method if you want to work on the original cells.

To remove parentheses from our given dataset, follow the steps outlined below:

  1. Navigate to Home-> Find & Select-> Replace. Alternatively you can press the CTRL+H shortcut on your keyboard.
Click on the Replace option
  1. This opens the Find & Replace dialog box.
  2. Type the symbol ‘(‘ in the input box next to ‘Find what’, and leave the input box next to ‘Replace with’ blank. This makes sure that Excel deletes the ‘(‘ symbol from your selected cell’s content.
enter opening parenthesis in Find what
  1. Press the Replace All button if you want to replace all instances of the ‘(‘ symbol. If you want to only remove the ‘(‘ from the selected cell then press the ‘Replace’ button.
Keep replace with empty
  1. Repeat steps 3 and 4 to remove the closing parenthesis symbol ‘)’ in the same way.

In our example, you should now have all parentheses removed from the worksheet.

Dataset where parenthesis have been removed

Using the SUBSTITUTE Function to Remove Parentheses in Excel

The above method makes changes to the original cells.

However, if you prefer to keep the original data intact, then a better way to remove parentheses would be to use a formula.

In excel, the SUBSTITUTE function is used to replace a set of characters with another. The syntax for the function is as follows:

SUBSTITUTE(text, old_text, new_text,[nth_appearance])

Here,

  • text is the string or reference to the cell containing the string that you want to work with
  • old_text is the character or set of characters that you want to replace
  • new_text is the character or set of characters that you want to replace old_text with
  • nth_appearance is the number of the instances of old_text that you want to replace. This parameter is optional. If it is omitted then every occurrence of old_text will get replaced with new_text.

The SUBSTITUTE function simply takes the text, replaces one or all occurrences of old_text with new_text, and returns the resultant string obtained.

Let us use this function to remove parentheses from all the cells in our given dataset.

  1. In cell B2, type the following formula: =SUBSTITUTE(A2, “(“,””)
  2. Press the return key and copy this formula down to the rest of the cells in column B (using the fill handle). You should now find all the opening parenthesis symbols removed from cells in the range A2:A9.
SUBSTITUTE function to remove fist parenthesis
  1. Next, in cell C2, type the following formula: =SUBSTITUTE(B2, “)“,””)
  2. Repeat step 2. You should now find all the parenthesis symbols removed from cells in the range A2:A9.
Formula to remove the second parenthesis
  1. Copy the cells of column C and paste by Value (CTRL+Alt+V) onto column B if you want to retain the original strings of column A. If you want to write on top of the original strings, then paste by value on to column A.
  2. You can now go ahead and remove all the extra columns created. 
Also read: Show Negative Numbers in Parentheses/Brackets in Excel

Using VBA to Remove Parentheses in Excel

In both the above methods, you will notice that we had to remove each symbol of the parentheses one at a time.

If you often need to perform this task, you can save time by using a simple macro that removes both parentheses with a single click.

Here’s the code that you can use to implement the Macro:

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

This code takes a selected range of cells and replaces both the opening and closing parentheses signals from the text in these cells.

To apply this script, follow the steps shown below:

  1. From the Data tab, select Visual Basic.
Click on Visual Basic
  1. When the VBA window opens, click Insert->Module.
Click on Insert module
  1. Copy the code provided above and paste it into the module window.
Copy-paste the code in the VBA code window
  1. Close the window.
  2. Now return to your worksheet and select the cells that you want to work with (cells  A2:A9 in our case).
  3. From the Developer menu, select, Developer->Macros.
Click on macros option
  1. This will display the Macros dialog box. Select the macro with the name RemoveParentheses.
Select the macro name that you want to run
  1. Click Run.

Your code should now run and remove all the parentheses from your selected cells.

Dataset from which the parenthesis have been removed

Note: You can also add the shortcut to the VBA to the Quick Access Toolbar (QAT). This will let you run the script every time you need to, by just clicking a single button.

Also read: How to Remove Formulas in Excel (and keep the data)

How to Remove Parentheses and their Inside Content in Excel

In some cases, you might need to remove the parentheses along with the content inside them.

This can also be achieved quite easily. Here are two ways to remove parentheses along with their contents:

  • Using Find and Replace (with wildcard *)
  • Using the LEFT and FIND functions

Using Find and Replace to Remove Parentheses and their Inside Content in Excel

The Find and Replace method can again be used to remove parentheses, along with their contents.

For this, we need to use a wildcard that represents the text inside the parentheses symbols.

A 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.

So the string ‘s**r’ can match with the strings ‘star’, ‘stir’, ‘scar’, ‘soar’ as well as ‘sir’. 

This wildcard can therefore be used in your Find and Replace dialog box to easily match all instances of strings of the format ‘(*)’.

In other words, all instances of parentheses and the content inside them.

To remove parentheses and the content inside them using the Find and Replace feature, follow the same steps mentioned in the first section of this tutorial, with the exception of the string entered inside the input box next to ‘Find What’:

  1. Press CTRL+H from your keyboard
  2. Type the string “(*)” in the input box next to ‘Find what’.
Enter (*) in the find what field
  1. Press the Replace All button

You should now have all parentheses (along with the text inside them) removed from the worksheet.

parenthesis and the text in the bracket has been removed
Also read: Remove Negative Sign in Excel

Using the LEFT and FIND Functions to Remove Parentheses and their Inside Content in Excel

If you prefer using a formula instead of directly working on the original data, then a combination of the LEFT and FIND functions can help you in removing parentheses from your data along with the text inside the parentheses.

First, let us understand what each of these functions does individually.

The LEFT Function

The LEFT function is used to extract the first n character(s) in a text string starting from the left.  The syntax for the LEFT function is as follows:

LEFT(text, [num_chars])

Here,

  • text is the string or reference to a cell containing the string you want to work on
  • num_chars is the number of characters that you want to extract from the left of text.

The LEFT function takes the text, extracts num_chars number of characters from the left of the string, and returns the extracted characters.

The FIND Function

The FIND function is used to find the position of a given substring in a string. The syntax for the FIND function is as follows:

=FIND (find_text, within_text, [start_num])

Here,

  • find_text is the string that you want to match.
  • within_text is the text that you want to search in.
  • start_num is the position in within_text that you want to start your search from. This parameter is optional.

The FIND function basically searches within_text for the search string find_text and returns a number representing the position of this search string in within_text.

To remove parentheses and the content inside them using a formula, all you need to do is enter the following formula in cell B2 and then drag down the fill handle:

=LEFT(A2,FIND("(",A2,1)-1)

The above function simply extracts all the characters that occur before the opening parentheses in each cell. The result of this can be seen in the screenshot below:

Left formula to extract text to the left of the parenthesis

Explanation of the formula

Let us understand how this formula worked by breaking it down, starting from the inner function:

=FIND("(",A2)

The above formula simply finds the position of the opening parenthesis, starting from the first position of the text. For the first text (cell A2), this returns the value 7.

We want to extract all the characters in the text in A2 that come right before the opening parentheses (or all the characters that are to the left of the opening parenthesis symbol). For this we use the following formula:

=LEFT(A2,FIND("(",A2,1)-1)

The 7th position in the text is the position of the opening parentheses. We want to extract everything before this symbol.

In other words, we want only the first 6 letters starting from the left of the string in cell A2. That’s why we subtract 1 from the output of the FIND function.

The end result is the first 6 characters from A2, or the string “Smith”.

In this tutorial, we showed you how to remove parentheses from the text in two cases – when you want to remove only the parentheses symbols and when you want to remove the parentheses along with the text that they enclose.

We hope you found the steps outlined in this tutorial straightforward and easy to apply.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment