How to add/insert prefix or suffix to range of cells in Excel?
Sometimes you need to add a specific prefix or suffix to multiple celss for some reasons. Of course, you can add the prefix or suffix to each cell one by one. However, work goes hard and time-consuming when there are numerous cells. This article will show you some tips about adding prefix or suffix to selected range of cells in Excel easily.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
Supposing you have an example of fruit list, this article will guide you to add prefix, suffix, or both of prefix and suffix for the fruit list.
|With prefix||With suffix||With prefix and suffix|
The Excel's concatenate function can insert prefix or suffix for a single cell quickly.
1. Enter the function of = CONCATENATE("Food - ",A1) in a blank cell, such as Cell C1 in this case. See screenshot:
2. Click the Cell C1, and drag the fill handle across the range that you want to fill. And all of the cells have been added the specific prefix text. See screenshot:
3. As they are formulas, when you copy them, you need copy and paste them as values.
You can use the concatenate function to insert prefix, suffix, or both of suffix and prefix as follows:
Results in cells
= Concatenate ("Food - ", A1)
Food - Apple
=Concatenate (A1, " - Fruit")
Apple - Fruit
=Concatenate ("Food - ", A1, " - Fruit")
Food – Apple - Fruit
Note: you can also use the function of = ("Prefix"&A1&"Suffix") to add a prefix or suffix for cells.
You can also deal with this problem with the following VBA code:
1. Select the range that you want to insert the prefix or suffix.
2. Click Developer > Visual Basic, and a new Microsoft Visual Basic for applications window will display, click Insert > Module, and then input the following code in the Module:
VBA: Add subfix to the text:
Sub AddTextOnRight() 'Updateby20131128 Dim Rng As Range Dim WorkRng As Range Dim addStr As String On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) addStr = Application.InputBox("Add text", xTitleId, "", Type:=2) For Each Rng In WorkRng Rng.Value = Rng.Value & addStr Next End Sub
3. Then click button to run the code, and a prompt box will be appeared for you to select a range. See screenshot:
4. And then click OK, another prompt box will be popped out, in the box, input the specific suffix text. See screenshot:
5. Click OK, all of the selected cells have been added suffix.
VBA: Add prefix to the text:
Sub AddTextOnLeft() 'Updateby20131128 Dim Rng As Range Dim WorkRng As Range Dim addStr As String On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) addStr = Application.InputBox("Add text", xTitleId, "", Type:=2) For Each Rng In WorkRng Rng.Value = addStr & Rng.Value Next End Sub
Please follow the same steps as shown as above, you can get the results as below, see screenshot:
You may be not familiar with functions and VBA code in Excel. And this Add Text utility of Kutools for Excel will help you insert prefix or suffix to any selected ranges quickly.
Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.
1. Select the cells that you want to work with.
2. Click Kutools > Text Tools > Add Text, see screenshot:
Step 3: In the Add Text dialog box, enter your prefix or suffix in the Text box, specify settings and click OK.
- If you want to add prefix for the selected range of cells, check the Before first character option;
- If you want to add suffix in the selected range of cells, check the After last character option.
The Add Text utility of Kutools for Excel supports us to add any specific text and characters before or after the original cell text. What's more, we can add specific text between original cell texts. No matter which settings you set, you can preview the cell changes in the Preview section. Click to know more about this utility.
- Add text to the beginning or end of all cells
- Add strings of text to multiple cells
- Add/insert leading zeros to numbers or text