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

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
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.

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

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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:


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

0#The O2013-12-20 18:47
Thanks, Kutools saved me a lot of work :-)
Reply | Reply with quote | Quote
0#John Graf2014-01-23 23:24
Thank you, the above instructions have saved me hours of work.
Reply | Reply with quote | Quote
0#Vishal2014-01-24 08:02
CAn i add KUltools without admin rights
Reply | Reply with quote | Quote
0#Admin_jay2014-01-25 02:50
Quoting Vishal:
CAn i add KUltools without admin rights

:-) You need to install the software with an admin account. After the installation, you can use it without admin account.
Reply | Reply with quote | Quote
+1#Simensch2014-03-12 10:58
Thanks!
= CONCATENATE made my day!
Reply | Reply with quote | Quote

Add comment


Security code
Refresh