Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


4 Simple Methods to Add Prefix to Entire Column in Excel

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. Excel‘s 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.

STEPS:

  • First of all, Choose the D5 cell to begin.
  • Second, Insert the following formula in cell D5:
=CONCATENATE(B5," ",C5)
  • 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 Prefix 0 in Excel (7 Easy Methods)


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.

STEPS:

  • 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

Read More: How to Remove Prefix 91 in Excel (4 Easy Ways)


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.

STEPS:

  • To get started, choose the cell.
  • Second, type the following formula into cell D.
=C5
  • 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 Remove Prefix in Excel (6 Methods)


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.

STEPS:

  • 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
Next
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


Conclusion

To Add a Prefix to the Entire Column in Excel, you can now use the methods we’ve already discussed. On the ExcelDemy Website, there are many articles like this one. 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

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo