Sometimes, when working with text data in Excel, you may want to add parentheses around the text in a cell or range of cells.
For example, below, I have a data set with the region name in column A, and I want to put these names in parentheses or brackets.
As always, Excel offers multiple ways to do this easily.
In this article, I will show you four different methods you can use to quickly add parentheses around text in a range of cells in Excel.
Add Parentheses Around Text Using Flash Fill
The fastest way to quickly add parentheses in cells is by using Flash Fill.
Flash Fill works by identifying patterns based on the result that you manually enter in one or two cells and then use it to fill the entire column following the same pattern.
Let me show you how it works.
Below is the data set where I have country names in column A, and I want to add parentheses around these names.
Here are the steps to do this using Flash Fill:
- In cell B2, manually enter the result that you expect (which would be (US) in this case)
- Now, hold the Control key and press the E key. This is the keyboard shortcut to run Flash Fill, which would fill the entire column based on the pattern that it has identified in cell B2.
If you don’t want to remember a new keyboard shortcut, you can also run Flash Fill by going to the Home tab, clicking on the Fill option, and then clicking on Flash Fill.
Note that the result you get with this method is not dynamic. So, if your original data in the column changes, the result will not automatically update, and you’ll have to use Flash Fill again to get the updated results.
In some cases, it’s possible that Flash Fill may not be able to recognize the pattern correctly. In such cases, you can enter the expected result manually in two or three cells and then try using Flash Fill.
Also read: Remove Parentheses in Excel
Add Parentheses Around Text Using Formula
You can also use a simple concatenate formula to quickly add parentheses around text in a range of cells.
Below, I have the same data set where I have country names in column A, and I want to get these names within parentheses.
Here is the formula that will do this:
Enter this formula in cell B2, then hit enter, and then copy the formula down for all the cells to get the results.
If your version of Excel has dynamic arrays, then you can also use the following formula (with no need to copy down the formula as it automatically sills and fills the entire range):
One advantage of using this formula method is that your result is dynamic. So, if the original data in column A changes, the result in Column B will automatically update
Add Parentheses Around Text Using Custom Cell Formatting
One smart technique to add parentheses around text without changing the actual text in the cell is by using custom cell formatting.
With this method, we change the cell format so that it shows you the parentheses around the cell content, but it doesn’t actually add them to the cell.
Let me show you how it works.
Below is the data set where I have country names in column A, and I want to display these country names within parentheses.
Here are the steps to do this using custom cell formatting:
- Select the range of cells to which you want to add parentheses
- Hold the Control key and then press the 1 key. This will open the Format Cells dialog box. Alternatively, you can also click on the Home tab and then click on the dialog box launcher in the Number group.
- In the Number tab, click on the Custom option
- Enter the following format in the Type field:
- Click ok
As soon as you click ok in step 5, you will notice that an opening and closing parenthesis have been added around the cell content in the selected range.
But if you select any of these cells, you will notice that in the formula bar, it shows you the cell values without parentheses.
Unlike the other methods covered above, in this method, you do not need an additional column to get the result. Also, if you change the content of any cell to which this format has been applied, it will automatically update and put the new cell content within brackets.
Also read: Display Negative Numbers in Parentheses
Add Parentheses Around Text Using VBA
If adding parentheses around the text is something you need to do quite often, you can also consider creating a VBA macro script and adding it to your workbook (or personal macro workbook).
Below is the VBA code that adds an opening and closing parenthesis to the cells in the selected range:
'Code by Sumit Bansal from Trumpexcel.com
For Each cell In Selection
cell.Value = "(" & cell & ")"
The above VBA code uses a For Each Next loop to go through each cell in the selection and add an opening and closing parenthesis around the cell content
Here are the steps to use this macro code:
- Press Alt + F11 on your keyboard (or click the Developer tab and then click on the Visual Basic icon). This will open the Visual Basic for Applications (VBA) editor.
- In the Visual Basic Editor, click on the Insert option in the menu and then click on Module. This will insert a new module for that workbook.
- Copy and paste the above VBA code into the Module code window. If the module code window is not open or you have closed it accidentally, double-click on the module object in the Project Explorer, and it will reopen the module code window.
- Now, to run the macro code, Place the cursor anywhere in the code and press the F5 key, or click on the run macro icon in the toolbar.
- Close the VB Editor.
Once you have the code in the workbook, you can reuse it by clicking on the Developer tab, clicking on the Macros button, and then selecting the macro and clicking the Run button.
Important Note: Remember that any changes that are done by a vb macro cannot be undone. So, I strongly suggest you create a backup copy of your data set just in case you need the original data set back.
If you want to reuse the macro in the workbook, ensure that you save it as a macro-enabled file (with .XLSM extension). This option appears in the Save As dialog box when saving the file.
So, these are four methods that you can use to quickly add parentheses around text in a cell or range of cells in Excel.
I’ve also created a table below that briefly summarizes each method along with some of the important things you need to know.
|Using Flash Fill
|Quick and Easy
|The result is static. Flash Fill may recognize a wrong pattern sometimes
|Result is dynamic, so it updates if you change the original dataset
|Using Cell Formatting
|Parentheses are not actually added (but only displayed in the cell. Result is dynamic.
|More complicated than other methods
|Useful when you need to repeat this often
|Needs some setting up
I hope you found this Excel article helpful. Do let me know your thoughts while leaving a comment below.
Other Excel articles you may also like: