How to Add Prefix to Entire Column in Excel (4 Simple Methods)

Microsoft Excel is undoubtedly one of the most essential and valuable computer programs today. Using Excel’s tools and features, we can do any number of calculations on any data. This article shows four basic ways to add prefixes to a column in Excel. Therefore, use these 4 Simple Methods to add Prefix to the Entire Column in Excel.

How to Add Prefix to Entire Column in Excel: 4 Simple Methods

For example, the following dataset includes three columns labeled Prefix, Name, and Prefix Name, respectively. The values of the Name Columns include some well-known names of professors from different universities around the United States. On the other hand, we go with “Prof.” as the value for the prefix. We will finish the Name by concatenating the Prefix and Name columns together. Using all these 4 methods, we’ll add Prefix in Excel. I haven’t said anything yet, but I’m writing this essay using Microsoft Excel 365. That’s another thing I have yet to mention. You are, however, at liberty to utilize any other edition that affords you the most significant degree of commoditization in the manner that best suits your needs.

how to add prefix to entire column in excel

1. Apply CONCATENATE Function to Add Prefix in Excel

Concatenate is defined as joining or combining.  the CONCATENATE function connects text fragments or combines data from numerous cells into a single cell. The CONCATENATE Function is our primary prefix insertion choice. To effectively finish the task, follow the steps listed below.


  • First of all, Choose the D5 cell to begin.
  • Second, Insert the following formula in cell D5:
  • Thirdly, hit Tab or Enter.
  • Therefore, it will return the expected result, Edmund Phelps.

Apply CONCATENATE Function to Add Prefix in Excel

  • Furthermore, use the formula in cell D5 to add prefixes to other cells.
  • To achieve this effect, click the Fill Handle icon.
  • Importantly, hold it while also dragging it to Cell D10.
  • As illustrated below, it will return the corresponding output as a result.

Output of applying CONCATENATE Function to Add Prefix in Excel

Read More: How to Add Suffix in Excel

2. Use the Ampersand (&) Operator in Excel to Insert Prefix

Combine two or more text strings into a single text string using the Ampersand (&) operator. Excel refers to this symbol as the Text Concatenation Operator. We will use the ampersand operator to add a prefix in this case. Please follow the guidelines outlined below to accomplish the task efficiently.


  • First, start by picking the cell.
  • Subsequently, write the following formula in cell D.
=B5&" "&C5
  • Later, use either the Tab or Enter key.
  • As a result, you’ll get the output, which is Edmund Phelps.

Use the “&” Operator in Excel to Insert Prefix

  • Presently, apply the formula used in cell D5 to other cells to add prefixes.
  • To accomplish this, click the Fill Handle symbol.
  • Importantly, hold it down and move it to cell D10.
  • Thus, it will produce the desired result, as shown below.

Output of using the “&” Operator in Excel to Insert Prefix

3. Assign Prefix with Format Cells Attributes

With the Format Cell attributes, we can change how the data in a cell looks in the worksheet. It’s important to remember that it only changes how the data is shown, not what it means. Excel also lets you add prefixes with the help of the Format Cell attributes feature. Carefully follow the instructions below to get the job done right.


  • To get started, choose the cell.
  • Second, type the following formula into cell D.
  • Afterward, tap either the Tab or Enter key.
  • Consequently, you will receive the desired consequence.

Assign Prefix with Format Cells Attributes

  • Important, you must keep it pressed down and slide it to cell D10.
  • Therefore, it will have the impact that was intended for it to have.

  • At this time, pick the D5 cell again.
  • Then, hold down the Shift key and press the Down Arrow key to choose the range from D5 to D10.

  • Later, right-click anywhere inside the range.
  • After that, choose the Format Cells option.

  • Because of this, the Format Cells window will pop out.
  • Now, go to Custom.
  • Then, write “Prof. “@ as a prefix in the Type Section.
  • After that, hit OK.

  • As a result, it will produce the desired outcome, as seen in the following.

Output of assigning Prefix with Format Cells Attributes

Read More: How to Add Prefix Without Formula in Excel

4. Add Prefix Through Excel VBA

VBA is an acronym for Visual Basic for Applications. Microsoft invented the programming language VBA. In particular, the VBA programming language permits access to Excel-incompatible features. In this part, we will insert prefixes into Excel using VBA. Please follow the directions below to complete the assignment correctly.


  • To begin, choose the current worksheet sheet.
  • Then, choose Developer from the menu.
  • Next, choose Visual Basic.

Add Prefix Through Excel VBA

  • Later, click Insert, followed by Module.

  • Presently, put the code below into the Module Box.
Sub SOFTEKOaddPrefix()
Dim i As Integer
Dim name
Dim prefix
Dim email
For i = 5 To 10
prefix = Cells(i, 2).Value
name = Cells(i, 3).Value
Cells(i, 4).Value = prefix & " " & name
email = Cells(i, 4).Value
End Sub
  • Subsequently, click F5 or select the Run button to finish.

  • As a consequence of this, the following will occur.

Output of adding Prefix Through Excel VBA

Read More: How to Remove Prefix in Excel

Download Practice Workbook

The sample workbook used during the workshop is free to use.


To Add a Prefix to the Entire Column in Excel, you can now use the methods we’ve already discussed. Please let us know if you have any other ideas or ways to get the job done. Please use the section below to ask questions, comment, or make suggestions.

Related Articles

<< Go Back to Suffix and Prefix | Text Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF