If you are dealing with a large spreadsheet, the act of manually adding a Suffix or Prefix to each and every cell of an Excel spreadsheet can take a very long time and lead to errors.
Hence, we are providing below the steps to quickly add Suffix or Prefix in an Excel spreadsheet using “&” Operator and Concatenate Function as available in Microsoft Excel.
Add Prefix or Suffix in Excel
You can find the steps below to add Prefix and Suffix in Microsoft Excel, using the “&” opearor and the Concatenate Function.
1. Add Prefix in Excel Using “&” Operator
Perhaps the easiest way to add Prefix or Suffix in Microsoft Excel is to make use of the “&” Operator.
In order to explain this better, take the case of an Excel spreadsheet containing the Names of doctors in Column A and the task is to add the Prefix Dr. to each and every name in Column A.
To add the Prefix (Dr.), place the cursor at Column B, type =”Dr. “&A4 and hit the enter key on the keyboard of your computer.
Tip: Instead of typing A4 you can type =”Dr. “& > move the cursor to cell A4 and hit the enter key.
After adding the Prefix (Dr.) to the first cell, you can quickly Add Prefix to all the cells by dragging the formula down to all the cells in column B.
2. Add Prefix in Excel Using Concatenate Function
Another way to add Prefix or Suffix to a group of Cells in Excel is to make use of the “Concatenate” function as available in Microsoft Excel.
To add Prefix (Dr.) using Concatenate function, type =Concatenate(“Dr. “,A4) and press the enter key on the keyboard of your computer.
Tip: Instead of typing A4 in above formula, you can move the cursor to A4 cell
Once Prefix is added to the first cell, you can quickly add this common Prefix to all the remaining Cells in the Excel spreadsheet by dragging the formula to all the remaining cells.
3. Add Suffix in Excel Using “&” Operator
In this case, let us assume that you are required to Add the Suffix “PHD.” to all the cells in Column B, so that names read in the format Dr. Name, PHD.
To add Suffix, place the cursor in Column C, type =B4&”, PHD.” and hit the enter key on the keyboard of your computer.
Tip: Instead of typing B4, you can type = Move the cursor to cell B4, type &”, PHD. and hit the enter key.
After adding the suffix (PHD.) to the first cell, you can quickly add this common Suffix to all the other Cells by dragging the formula down to all the Cells in column C (See image below).
4. Add Suffix in Excel Using Concatenate Function
Again using the above example, let us add the Suffix “PHD.” to the end of all the names in Column B using Concatenate function.
To do this, place the cursor in Column C and type =Concatenate(B4,” PHD.”) and hit the enter key on the keyboard of your computer.
After adding the Suffix in the first cell, you can quickly add the Suffix to all the remaining cells by dragging the formula to all the remaining cells.