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 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 formulas

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

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

Remove prefix or suffix from all cells in selection

Easily add bullets or same text string (prefix/suffix) to all cells in the specified range in Excel

It’s easy to fill all cells with same content in a column with the AutoFill feature. But, how to add same bullets, or same text strings (prefix/suffix) to all cells in the specified range? Comparing to typing same text string or symbols to each cell separately, Kutools for Excel’s Add Text feature provides an easy workaround to get it done with several clicks only. See screenshot:

ad add text bullets

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More      Free Download Now


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

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, says Cell C1, and then drag this cell's AutoFill handle across the range that you want to fill. And all of the cells have been added the specific prefix text. See screenshot:. See screenshot:

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

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

VBA: Add suffix 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 Run button or press the F5 key to run the VBA.

4. And now please speicfy the range you will add prefix or suffix for into the first KutoolsforExcel dialog box and click the OK button, and then type the suffix or prefix you will add into the second KutoolsforExcel dialog box and click the OK button. See below screenshots:

     

Now the specified suffix or prefix has been added into each selected cell as below screenshot shown:


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 - Combines More Than 120 Advanced Functions and Tools for Microsoft Excel

1.  Select the cells that you want to add prefix or suffix, and click  Kutools > Text > Add Text, see screenshot:

2. In the Add Text dialog box, enter your prefix or suffix in the Text box, check the Before first character option (for adding prefix) or After last character option (for adding suffix) as you need, and cick the Ok button.

And now the specified prefix or suffix is adding to each selected cells at once. See screenshot:

Free Download Kutools for Excel Now

Remove prefix or suffix (same number of characters) from beginning/right/end of cells in Excel

Kutools for Excel's Remove By Position feature eases you removing the preifx or suffix (certain number of characters) from beginning/end/specified position of text string in cells.

ad remove prefix suffix

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Read More      Free Download Now

arrow blue right bubbleRelated articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

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 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • 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

Add comment


Security code
Refresh