How to add prefix or suffix to a range of cells in Excel

If you need to add a certain prefix or suffix to a list across a column in Microsoft Excel or Excel online, then this article will be helpful. For instance, if we are presented with a long list of names and we 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 could 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 intend 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

Add prefix or suffix to a range of cells in Excel

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.

Pull the formula

Hit Enter when done and it will add the prefix to every entry in the intended column.

The result of prefix

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)

Prefix using Concatenate function

How to add a suffix to multiple (or all) entries across a column

We could 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"

Suffix using & function

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 where you need to add the suffix for the cells in the intended column.

Pull the formula down

Hit Enter when done and it will add the suffix to every entry in the intended column.

Result after adding suffix

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")

Using Concatenate for suffix

Use spaces while adding the prefix and suffix accordingly.

Hope this helps!

Posted by on , in Category Office with Tags
Karan Khanna is a passionate Windows 10 user who loves troubleshooting Windows problems in specific and writing about Microsoft technologies in general.