Sometimes, we have a dataset in Excel and we may want to add a common Prefix to all the data. We can do that quite simply in Excel without even using a formula. Here, I will show 2 easy ways to add a prefix without a formula in Excel. In addition, I will show some methods to add prefixes and suffixes to data in Excel.

Download Practice Workbook

You can download the practice workbook from here.

2 Easy Ways to Add Prefix Without Formula in Excel

Here, I will show 2 easy ways to add a prefix to data in Excel. Obviously, I will avoid any kind of formula in the procedures. To explain the first method, we will use a dataset that contains some Numbers. And in the second method, we will use a dataset that contains some Names. So, without further delay, let’s start the discussion.

1. Add Prefix Without Formula Using Number Format in Excel

If you want to add a prefix to numbers, you can do that easily by changing the number format of the cells.

Follow the steps given below for the procedures.

• Firstly, select the range of data where we will add a prefix.

• Then, go to the Home tab and select More Number Formats from Number Format.

• After that, select the Custom option.
• Now, type any prefix in front of General. In my case, I have typed k.
• Later on, press OK.

• As a result, we will see the prefix “k” added to the selected cell’s numbers.

2. Apply Excel VBA to Add Prefix Without Formula

We can apply a simple Visual Basics for Applications (VBA) code to add prefixes to the dataset in Excel. Data type doesn’t matter the in this case. Please follow the steps given below for the procedures.

• First, select the dataset where we will add prefixes.

• Now, open the VBA window by pressing Ctrl + F11.
• After that, from VBA projects select the active worksheet and right-click on it.
• Further, select Module from the options.

• In the module window, write the following code.

Sub AddPrefix()
Dim iRg As Range
Dim iWkRg As Range
Dim xaddStg As String
On Error Resume Next
xTitleId = "Add_Prefix"
Set iWkRg = Application.Selection
Set iWkRg = Application.InputBox("Selected Range", xTitleId, iWkRg.Address, Type:=8)
xaddStg = Application.InputBox("Add Prefix", xTitleId, "", Type:=2)
For Each iRg In iWkRg
    iRg.Value = xaddStg & iRg.Value
Next
End Sub

• Later, click on Run.

• In the Add_prefix box, write the range of cells for adding prefixes.
• In our case, we selected the range previously, so the box will be filled automatically.

• Press OK, and in the next appeared box write the desired prefix.
• Again, press OK.

• Finally, come back to the worksheet and we will see the prefix added to the value of the selected cell.

How to Add Prefix and Suffix with Formula in Excel

We can add prefixes as well as suffixes to the data in Excel. In this section, I will show the procedures to do that.

1. Apply CONCATENATE Function to Add Prefix and Suffix in Excel

We can use the CONCATENATE function of Excel to add prefixes or suffixes to data in Excel. Follow the procedures given below.

• Suppose, we want to add a prefix to the value of Cell B5. firstly, write the following formula in Cell C5.

• Simultaneously, press Enter.

• Finally, use the Fill Handle to copy the formula in the cells below.
• We will see prefixes added to all the cell values.

• We can also add suffixes in a similar fashion using the CONCATENATE function. You should follow the same steps except for a slightly changed formula.
• The formula is given below.

2. Utilize "&" Operator to Add Prefix and Suffix in Excel

We can also utilize the “&” operator for adding a prefix or suffix to the data in Excel. Follow the steps given below.

• Firstly, write the following formula in Cell C5.

• Then, press OK.
• After that, use the AutoFill option of Excel to copy the formula in the cells following.

• In a similar fashion, we can add a suffix in Excel with a slightly different formula.
• Use the following formula.

Conclusion

Many times we require to add the same prefix to many data in Excel. Here, I have shown 2 easy ways to add a prefix in Excel without using a formula. If you face any problem following the procedures, please leave a comment. Visit our ExcelDemy Website for similar articles regarding Excel.

The post How to Add Prefix Without Formula in Excel (2 Easy Ways) appeared first on ExcelDemy.