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.

Add/insert prefix or suffix to multiple cells with functions

Add/insert prefix or suffix to multiple cells with VBA code

Add/insert prefix or suffix to multiple cells with Kutools for Excel

Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

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.

doc-add-prefix-subfix1
-1-2-3
doc-add-prefix-subfix2doc-add-prefix-subfix3doc-add-prefix-subfix4
With prefixWith suffixWith prefix and suffix

arrow blue right bubble Add/insert prefix or suffix to multiple cells with functions

Hint


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:

doc-add-prefix-subfix5

2. Click the Cell C1, and drag the fill handle Description: Selected cell with fill handle across the range that you want to fill. And all of the cells have been added the specific prefix text. See screenshot:

doc-add-prefix-subfix6

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:


Enter formulas

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.


arrow blue right bubble Add/insert prefix or suffix to multiple cells with VBA code

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 doc-multiply-calculation-3 button to run the code, and a prompt box will be appeared for you to select a range. See screenshot:

doc-add-prefix-subfix13

4. And then click OK, another prompt box will be popped out, in the box, input the specific suffix text. See screenshot:

doc-add-prefix-subfix12

5. Click OK, all of the selected cells have been added suffix.

doc-add-prefix-subfix11doc-arrow-rightdoc-add-prefix-subfix14

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:

doc-add-prefix-subfix11doc-arrow-rightdoc-add-prefix-subfix15

arrow blue right bubble Add/insert prefix or suffix to multiple cells with Kutools for Excel

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:

doc-add-prefix-subfix9

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.

doc-add-prefix-subfix10

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.

Related articles:


Is your problem solved?

Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 More than 120 powerful advanced functions which designed for Excel:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more     btn download     btn purchase