When someone says suffixes and prefixes, we all think of the word to insert at the end or beginning, isn’t it? For Instance, words something like un+do: undo or help+ful: helpful might have popped up in your mind.
In Excel, although the suffix and prefix have the same meaning, the texts used in data are different. Here, you’ll mostly find the need to add Titles before a name or @gmail.com in the end. However, sometimes, it can just be the text strings that are separated into different columns like first name and last name.
To add suffixes or prefixes, you can use Excel’s Flash-Fill, Format cells, Ampersand symbol, or CONCATENATE functions.
Using Flash-Fill
Whenever you are performing tasks in Excel, you would definitely want to do it in the simplest and quickest way possible.
For this, Excel’s Flash-fill tool is the best as it does almost all the work for you when it recognizes a certain pattern. Meaning, if you input the value in the same format, Flash-fill will auto-fill the remaining cells for you. You just need to press enter to approve it.
Use this method to add a similar suffix or prefix to the data.
Add Suffix
Let’s assume, you need to add a suffix @gmail.com in the name. On a new column, type the name and suffix together. For example, [email protected] and press enter.
Now, in the second cell, start typing [email protected]. When you do this, the Flash-fill identify a similar pattern and automatically fills the rest of them. Press Enter to confirm.
Add Prefix
Suppose you need to add Prefix in the following data. On a new column, type in Mr.Louis and enter. Then, instead of re-typing the name in the second column, you can also simply press Ctrl + E to fill the remaining cells.
Using Format Cells
If you have the same suffix or prefix to add, you could use Format Cells. Here, in this method, we will create a custom format of suffixes or prefixes to apply to your value.
As an example we will add @gmail.com as a suffix and Mr. as a prefix in the given steps.
- Firstly, in a new column copy and paste the names.
- Select the Ranges with names and enter Ctrl + 1 for Format Cells.
- On the Number tab, head to Custom Category.
- Under Type enter the following format and click OK to confirm.
- For Suffix:
@"@gmail.com"
- For Prefix:
“Mr.”@
Using Ampersand Symbol
In the first two methods, we learned how to insert the same suffix and prefixes in the data. But, some of you may have a separate text to add.
For this, we will use the Ampersand symbol. Basically, this particular symbol is used to join two texts or columns in Excel.
Add Suffix
In the given example, we have Country names as a word and capital cities as Suffix. To combine them together, enter the formula given below in a new column.
=A2&" "&B2
You can also drag the Fill handle all the way down to copy the formula to other cells too.
Add Prefix
Say you have a list of first names as prefixes and last names in your sheet. To merge them with the Ampersand Symbol, enter the formula in a new column. Then, extend the fill handle to copy the formula to other cells too.
=A10&" "&B10
Using CONCATENATE Function
CONCATENATE function is just the formula version of the Ampersand symbol. Use this function if you have to add multiple suffixes and prefixes cell.
Syntax: CONCATENATE(text 1, text 2, text 3, ….)Add Suffix
Let’s assume, you have the time, hours, and seconds columns in different columns. To combine them enter the formula below.
=CONCATENATE(A12,B12, " ", C12,D12)
The above formula takes A12 as Text 1, B12 as Text 2, ” ” as Text 3 and combines all of them. Here, ” ” means we are adding a space in between the text.
Add Prefix
Suppose, you need to add prefixes in the name and then merge both values together. For this, type in the given formula in the cell and press enter.
=CONCATENATE(A2,B2, " ", A3,B3)
How to Add Both Prefixes and Suffixes to Cells in Excel?
In some cases, you may have to add both Prefixes and Suffixes to cells in Excel. In the example, you can see that Prefix is the name title and the Suffix is the Address. For this, we’ve entered the formula below.
=CONCATENATE(A2,B2, " ", A3,B3, ", ",C2)
In the formula, A2 is Text 1, B2 is Text 2, ” ” is Text 3, and so on. You might have also noticed, we added a “,” delimiter to return the comma after the name. In case you do not want to insert it, you can simply write your formula as “ ” that will return only space.