You can swiftly apply prefixes and suffixes to columns using advanced Excel functions.
If you are working on a large Microsoft Excel spreadsheet and need to add prefixes or suffixes and don’t want to do a manual task, then this section will help you to quickly and easily add the prefix and suffix to all cells in seconds.
When working with a large spreadsheet, adding a suffix or prefix to every cell might take a long time and leave you exhausted.
You can add a suffix or prefix in a few quick and simple ways, as stated in the section below, reducing the manual effort needed.
We’ll go through three different ways to add suffixes and prefixes to an Excel spreadsheet.
- “&” operator
- Concatenate Function
- Cell formatting option
1. Using the “&” Operator in Excel
In Excel, you can combine one or more text strings into a single piece of text using the ampersand (&).
Let’s look at an instance of using “&” in Excel to add a Prefix.
Adding Prefix in Excel using the “&” operator
As shown below in Excel, column B contains the names.
To add Prefix Mr. to each name, Place the cursor at C5, type =” Mr.” &B5, and press enter.
This step will add Prefix Mr. to that particular cell.
It is pretty simple to understand and implement.
To add Prefix Mr. to all cells or the entire column, drag the cell corner of C5 as shown below, adding Prefix Mr. to all cells.
You have successfully added a prefix to all cells in a few seconds. The Prefix Mr. can be replaced with any prefix as per the requirement.
Adding Suffix in Excel using the “&” operator
Let’s see how to add Suffixes using “&” in Excel.
As shown in the below Excel, column B contains the names of the vegetables. The task is to add the Suffix “vegetables” to all cells.
Place the cursor at cell C5, type =B5 & “- vegetables,” and press enter.
It will add Suffix “vegetables” to the C5 cell.
Drag the Cell corner of C5 as shown below to apply suffixes to all cells. This adds suffixes to all cells.
You have successfully appended Suffixes to every cell in just a few seconds. Like prefixes, any suffix can be used instead of “vegetables”.
2. Using Excel’s concatenate function
The “concatenate” feature in Microsoft Excel is another quick and easy way to add prefixes and suffixes to many cells. The function “concatenation” combines two or more text strings to create a single string.
Here is an example of using Excel’s “concatenate” function to prefix characters:
Utilizing the concatenate function, add the prefix Mrs. as demonstrated in the example below:
Place the cursor where you want to add the prefix and type =concatenate(“Mrs.” &B5) and press enter key. This step will add the prefix Mrs. to that particular cell, as shown below.
To add Prefix Mrs. to all the cells, drag the cell corner of C5 as illustrated below to apply the prefix to all cells at once.
You have successfully appended prefixes to every cell in just a few seconds.
Add suffix using Concatenate Function
Let’s quickly review how to use the Concatenate Function to add the suffix “Ph.D”.
Put the cursor in Column C below, type =CONCATENATE(B5,”, Ph.D.”) and press the enter key.
To add Suffix Ph.D. to all the cells within a few seconds, drag the cell corner of C5 as shown below, and the Suffix Ph.D. will add to the entire column.
3. Using Cell Format properties
In the section above, we looked at how to add a prefix and a suffix in a different cell. This section will look at how to do it in the same cell.
It is quick and easy, so let’s get started:
Adding prefix using Cell Format properties
To Add Prefix Mr. using the format cell option, select all cells, right-click, and select the Format Cells option as shown in the picture below.
Right-clicking brings up the properties box, as seen in picture 1.
Enter the formula “Mr.” @ in the Custom option field, then click OK.
All cells will have the prefix Mr. inserted, as shown in picture 2.
Adding suffix using Cell Format properties
Let’s use the identical Excel sheet from above to do this task.
Let’s rapidly append the suffix Engg to every cell.
As we did when adding the prefix in the example above, select all the cells, then right-click and use the Format Cells option to add the suffix “Engg”.
Go to the custom option in the format cell properties, type the formula @, “Engg.” as shown in picture 1, and then click OK. All cells will have the suffix added, as shown in Picture 2.
Final Words
In this article, we learned a quick and easy way to avoid the manual procedure of adding prefixes and suffixes to several cells. This aids you in formatting the Excel file quickly.
