SUPPRIMER LES PARENTHÈSES DANS EXCEL

Remove parentheses in Excel (4 simple methods)

by

You’re given an Excel file to process and discover that it contains a large number of unnecessary brackets. How do you remove parentheses in Excel? You could decide to remove them one by one by selecting them and deleting them using the delete key, but this would be tedious and time-consuming, especially if you have a large dataset. In this tutorial, we look at the following 4 effective methods you can use to easily remove parentheses in Excel :

  1. Use the Find and Replace function.
  2. Use the SUBSTITUTE function.
  3. Use Excel VBA code.
  4. Use a combination of the LEFT and FIND functions.

We’ll use the following dataset to demonstrate how each of these methods can be applied.

1. Use the Find and Replace function to remove parentheses

This method is the simplest and most commonly used by many people. The Find and Replace function replaces all parentheses in the selected dataset with empty spaces. To apply this method, we proceed as follows: Step 1 – Select the dataset whose parentheses you wish to remove. Step 2 – Press the Ctrl key and then the H key to launch the next Find and Replace dialog box.

Alternatively, we can open the Find and Replace dialog box by navigating to Home>>Find and select>>Replace as shown below:

Step 3 – To remove the starting or opening parenthesis, type “(” in the Find what field and leave the Replace with input field empty, as shown below:

Step 4 – Click on the Replace all button to remove all opening brackets from the dataset. The following dialog box is displayed to indicate that the operation has been successful:

Click OK in the dialog box and the dataset now looks as follows:

Step 5 – Repeat steps 2, 3 and 4 to remove all closing brackets. In step 3, be sure to type ” )” in the Find whatinput field
. All brackets will now be removed and the dataset will look like this:

2. Using the SUBSTITUTE function

We use this method if we prefer to leave the original data set as is. The SUBSTITUTE function allows you to replace one set of characters with another. To remove all the brackets from your data set using the SUBSTITUTE function, proceed as follows: Step 1 – Select the data set whose brackets you wish to remove. Step 2 – In cell C2 , enter the formula =SUBSTITUTE(A2,” ( “,” “) as shown below :

Step 3 – Press Enter and use the fill handle to copy the formula to all the cells in column C. All opening brackets will be removed from the range C2:C8, as shown below:

Step 4 – In cell D2 , enter the formula =SUBSTITUTE(C2,” ) “,” “) as shown below:

Step 5 – Press Enter and use the fill handle to copy the formula to all cells in column D. All closing parentheses will be removed from the D2:D8 range, as shown below:

Step 6 – To retain the original values in column A, copy the contents of column D and paste them into column C by value(CTRL+Alt+V). Then delete column D and your dataset will look like this:

3. Using an Excel VBA macro

In the previous methods, we had to delete one parenthesis at a time, but with VBA code, we can delete all the parentheses at once. Follow these steps to remove parentheses from your dataset using VBA code: Step 1 – Press Alt+F11 and copy the following VBA code into a module.

Sub RemoveParentheses() Cells.Select Selection.Replace What:=” ( “, Replacement:=” “, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Replace What:=” ) “, Replacement:=” “, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub Step 2 – Select the data set whose parentheses you wish to remove. Step 3 – Press Alt+F8 to open the macro dialog box. Select and run the macro.

The macro will remove all brackets from your dataset at once.

4. Use a combination of LEFT and FIND functions.

Using a combination of the LEFT and FIND functions, we can remove parentheses from any data set. The LEFT function returns a specified number of characters from the beginning of a text string. The FIND function is case-sensitive and returns the starting position of a text string within another text string. Follow these steps to apply these functions to the removal of parentheses: Step 1 – In cell C2, enter the formula =LEFT(A2,FIND(“( “,A2,1)-1) as shown below:

Step 2 – Using the fill handle, copy the formula to the other cells in column C.

Explanation of formula: In cell C2, the FIND function locates the position number of the opening parenthesis from the beginning and returns the number 8. We subtract 1 from 8 so that the LEFT function retains only 7 characters from the left, returning Morris. The same process applies to column C.

Conclusion

In this tutorial, we’ve looked at four ways of removing parentheses in Excel. You can apply any or all of these methods, depending on what you’re working on.