5 Ways to Strip Parentheses from Excel Cells Instantly

The usage of parentheses in Excel spreadsheets can be for various purposes: formatting numbers for clarity, indicating negative values, or just for grouping related data. However, there might be instances where you want to strip these parentheses from your cells. Whether you're looking to clean your data or perform certain calculations without parenthesis, here are five methods to instantly remove parentheses from Excel cells.

Method 1: Using the Replace Feature

Excel’s Find and Replace tool can quickly remove parentheses from your cells:

• Select the range of cells from which you want to remove parentheses.
• Press Ctrl + H to open the Find and Replace dialog.
• In the ‘Find what’ box, type: ( and in the ‘Replace with’ box, leave it blank.
• Click ‘Replace All’, then repeat for the closing parenthesis ).

⚠️ Note: Make sure to check ‘Match entire cell contents’ if you want to target only cells with parentheses around the whole content.

Method 2: Using Excel Functions

If you want a more complex operation, you can use Excel functions:

• Select an empty cell next to the cell with parentheses.
• Enter the following formula:

  =SUBSTITUTE(SUBSTITUTE(A1, “(”, “”), “)”, “”)

• Drag the formula down to apply to all cells in the range.
• Once you’ve applied the formula, copy and paste values over your original data to remove parentheses permanently.

Method 3: VBA Macro to Remove Parentheses

For bulk operations or recurring tasks, a VBA macro can be handy:

• Press Alt + F11 to open the VBA editor.
• Insert a new module by clicking Insert > Module.
• Paste the following code:

  Sub RemoveParentheses()
      Dim rng As Range
      Set rng = Selection
      rng.Replace What:=“(”, Replacement:=“”, LookAt:=xlPart
      rng.Replace What:=“)”, Replacement:=“”, LookAt:=xlPart
  End Sub
  

• Run the macro by selecting the range of cells and executing the macro from the ‘Macros’ dialog (Alt + F8).

Method 4: Using Text to Columns

Text to Columns isn’t just for splitting text. You can also use it to remove unwanted characters:

• Select the range containing cells with parentheses.
• Go to the ‘Data’ tab and select ‘Text to Columns’.
• Choose ‘Delimited’ then click ‘Next’.
• Check the box for ‘Other’ and type the parenthesis ( or ).
• Click ‘Next’, then ‘Finish’ to remove the parentheses from the selected cells.

Method 5: Utilizing Power Query

If you’re working with large datasets, Power Query can help:

• Select your range of data.
• Go to the ‘Data’ tab, click ‘From Table/Range’.
• In Power Query Editor, click ‘Transform’ then ‘Replace Values’.
• Enter ( in ‘Find what’ and leave ‘Replace with’ blank, then repeat for the closing parenthesis ).
• Click ‘Close & Load’ to refresh the data in your spreadsheet.

Summing up, Excel provides numerous approaches to strip parentheses from your cells, each suited to different scenarios and user proficiency levels. From the simple Find and Replace to the more sophisticated Power Query, these methods ensure you can clean your data efficiently. Understanding these techniques enhances your data management skills and can make complex data manipulations much more straightforward.

Will using these methods change my original data?

+

Methods like Find and Replace, VBA, and Text to Columns will change the original data. If you want to keep your original data intact, consider using Excel functions or Power Query, then copy and paste values to a new column.

Can I remove other characters using these methods?

+

Absolutely. You can adapt these methods to remove any unwanted characters by specifying them in the ‘Find what’ field or altering the functions/macro code accordingly.

What should I do if I’ve accidentally removed the parentheses?

+

If you’ve removed parentheses and need to revert, use Excel’s undo function immediately (Ctrl + Z). For more complex operations, you might need to restore from an earlier version of the file or manually add them back.

Are there any caveats when using Power Query to remove parentheses?

+

Power Query is very powerful but requires understanding of the data transformation process. Any changes you make are applied step-by-step, so ensure you’re only altering the cells with parentheses, or you might inadvertently change other data.

Can I use these methods on Excel for Mac?

+

Yes, all these methods are compatible with Excel for Mac, though the shortcut keys might differ. VBA is available but slightly different in implementation.