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.
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.
- 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.
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 D cell.
- Subsequently, write the following formula in cell D.
- Later, use either the Tab or Enter key.
- As a result, you’ll get the output, which is Edmund Phelps.
- 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.
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 D cell.
- Second, type the following formula into cell D.
- Afterward, tap either the Tab or Enter key.
- Consequently, you will receive the desired consequence.
- 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.
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.
- 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.
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.