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

Method 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.

STEPS:

Select cell D5 and enter the following formula:

=CONCATENATE(B5," ",C5)

Press Tab or Enter.
It will return the expected result, Edmund Phelps.

Apply CONCATENATE Function to Add Prefix in Excel

Use the Fill Handle tool for the remaining cells.
The result will be as shown below.

Output of applying CONCATENATE Function to Add Prefix in Excel

Read More: How to Add Suffix in Excel

Method 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.

STEPS:

Enter the following formula in cell D5.

=B5&" "&C5

Press Tab or Enter.
You’ll get the output, Edmund Phelps.

Use the “&” Operator in Excel to Insert Prefix

Use the Fill Handle for the remaining cells.
The desired output will be as shown below.

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

Method 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.

STEPS:

Enter the following formula into cell D5.

=C5

Press the Tab or Enter key.
You will get the desired result.

Assign Prefix with Format Cells Attributes

Use the Fill Handle tool for the remaining cells.

Select the D5:D10 range.

Right-click on the selected range.
Choose Format Cells.

In the Format Cells window, go to Custom.
Enter “Prof. “@ as a prefix in the Type Section.
Click on OK.

It will produce the desired output as shown in the following image below.

Output of assigning Prefix with Format Cells Attributes

Method 4 – Add Prefix Through Excel VBA

Choose the current worksheet.
Choose Developer from the menu.
Choose Visual Basic.

Add Prefix Through Excel VBA

Click Insert, followed by Module.

Enter the following code into the Module.

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