If you need to add a prefix or suffix to a list across a column in Microsoft Excel or Excel online, this article will be helpful. For instance, if we are presented with a long list of names and need to add a title, say doctor, before it, how do we do it easily?
Add Prefix or Suffix to all cells in Excel
How to add a prefix to multiple (or all) entries across a column
We can use two functions to add a prefix to multiple (or all) entries across a column. The first one is using the & operator, and the second is using the CONCATENATE function.
The formula for the & operator is as follows:
=”<prefix> “&<cell number of first cell>
Where <prefix> is the prefix to be added and <cell number of first cell> is the location of the first cell in the column to which to add the prefix.
Eg. We are creating the list in column C. If the first cell in the column for which we need to add the prefix is A3, and the prefix is TWC, the formula would be:
="TWC "&A3
We would have to place this formula in cell C3 since it needs to be in the same row as the first entry, which needs the prefix.
Then click anywhere outside the cell. Now click on the cell again, and it will highlight the option to select more cells in the same column. Pull the list down corresponding to the entries for which you need the suffix.
Hit Enter when done, and it will add the prefix to every entry in the intended column.
Alternatively, you could use the CONCATENATE function for adding the prefix:
=CONCATENATE("<prefix>",<cell number of first cell>)
In the example mentioned above, the function would become:
=CONCATENATE("TWC ",A3)
How to add a suffix to multiple (or all) entries across a column
We can use two functions to add a suffix to multiple (or all) entries across a column. The first one is using the & operator, and the second is using the CONCATENATE function.
The procedure for adding a suffix using the & operator is as follows:
=<cell number of first cell>&"<suffix>"
Where <suffix> is the suffix to be added and <cell number of first cell> is the location of the first cell in the column to which to intend to add the suffix.
Eg. We are creating the list in column C. If the first cell in the column for which we need to add the suffix is A3, and the suffix is pending, the formula would be:
=A3&" pending"
We would have to place this formula in cell C3 since it needs to be in the same row as the first entry, which needs the suffix.
Click anywhere outside the cell and then back on the cell. Pull the formula down across the column till you need to add the suffix for the cells in the intended column.
Hit Enter when done and it will add the suffix to every entry in the intended column.
Alternatively, you could use the CONCATENATE function for adding the suffix:
=CONCATENATE(<cell number of first cell>,"<suffix>")
In the example mentioned above, the function would become:
=CONCATENATE(A3," pending")
Use spaces while adding the prefix and suffix accordingly.
I hope this helps!
Leave a Reply