Excel is by far one of the most complete and verbose spreadsheet management software out there. It’s got everything you could want to with your numbers, and then some.
In this article, we’re looking at how you can add prefixes and suffixes to cell data in Excel.
Also read: How to convert Seconds to Minutes in Excel?
How to add prefix in Excel?
We can use two functions to add a prefix to either multiple or all cells across a given column.
- Using the & operator.
- Using the CONCATENATE function.
Using the & operator
The base formula of adding a prefix using the & operator is as follows
=”<prefix> “&<First cell/Cell range>
As you can probably guess, prefix is the prefix to be added followed by the operator and the cell number of the first cell or a cell range.
In the below example, we’re adding +91 to the numbers row. Hence the command will be.
="+91 "&C5
Add this formula to a convenient cell location. We’ll pick D5. As soon as you enter the formula, you’ll see the value with the suffix. Now hold and drag from the cell’s bottom right until the last cell you want the prefix to be added and Excel will add the prefix value to all cells.
Also read: How to Spell Check in Excel?
Using the CONCATENATE function
Using the CONCATENATE function provides a rather neat way of adding prefixes to cell data. The basic syntax is as follows.
=CONCATENATE("<prefix>",<cell number of first cell>)
Using the same example as above, the command, in this case, would be this.
=CONCATENATE("+91 ".C5)
Add the formula to D5 as before and we’re good to go.
Also read: How to remove duplicates in Excel?
How to add suffix in Excel?
Adding suffixes in Excel is just as easy as adding prefixes. Once again, there are two ways you can do this.
- Using the & operator.
- Using the CONCATENATE function.
Using the & operator
The basic formula for adding suffixes is as follows.
=<cell number of first cell>&"<suffix>"
As you can see, this is essentially the reverse of what we did when adding prefixes with the & operator.
For the same example, now the suffix formula will be something like this.
=C5&"+91"
Add this formula to a cell in a convenient location and drag from there to add suffixes to as many cells as you want.
Also read: How to Record Macros in Windows 10?
Using the CONCATENATE function
The base formula is as follows.
=CONCATENATE(<cell number of first cell>,"<suffix>")
Once again, this is pretty much the reverse of what we did while adding a prefix using the CONCATENATE function.
The formula for adding a suffix in our case would be
=CONCATENATE(C5,"+91")
Also read: How to lock cells in Excel?
