Log in  \/ 
x
or
 Use Facebook account  Use Google account  Use Microsoft account  Use LinkedIn account
x
x
Register  \/ 
x

or
 Use Facebook account  Use Google account  Use Microsoft account  Use LinkedIn account

How to add prefix or suffix to range of cells in Excel?

Sometimes you need to add common text to the beginning or end of all cells in some cases. 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 prefix or suffix to all cells with formulas

Add prefix or suffix to all cells with VBA

Add prefix or suffix to all cells with Kutools for Excel

Remove prefix or suffix from all cells in selection

Easily add common text to the beginning or end of all cells (prefix or suffix) in Excel

It’s easy to fill all cells with same content in a column with the AutoFill feature. But, how to add same prefix or suffix to all cells in a column? Comparing to type the prefix or suffix to each cell separately, Kutools for Excel’s Add Text utility provides an easy workaround to get it done with several clicks only. Click for 60-day free trial!
ad add 

text prefix suffix


arrow blue right bubble Add prefix or suffix to all 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 bubbleAdd prefix or suffix to all cells with VBA

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 specify 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 prefix or suffix to all 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 click the Ok button.
And now the specified prefix or suffix is adding to each selected cells at once. See screenshot:


arrow blue right bubbleDemo: add prefix or suffix to multiple cells with Kutools for Excel

Tip: In this Video, Kutools tab and Enterprise tab are added by Kutools for Excel. If you need it, please click here to have a 60-day free trial without limitation!

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 prefix or suffix (certain number of characters) from beginning/end/specified position of text string in cells. Click for 60-day free trial!
ad remove prefix suffix

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 star1Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

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

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    daniyal ejaz · 28 days ago
    Hi,

    does any one how to add two numbers in one cell.
    For example,

    X
    2.2
    3.6
    0.9
    1.2

    and I want to add each number with its rank,

    Like;
    X
    2.2 (2)
    3.6 (1)
    0.9 (4)
    1.2 (3)
    and these rank must be bold and with different colour.
  • To post as a guest, your comment is unpublished.
    SANDEEP · 3 months ago
    thanks very much for KUTOOLS FOR EXCEL
  • To post as a guest, your comment is unpublished.
    Jasvanthi · 7 months ago
    I have filtered the cloumn and i have to apply TC001 till TC0209 on the filtered cells, could anyone please help me with this
  • To post as a guest, your comment is unpublished.
    Rajendra · 11 months ago
    i have single alphabets in different cell, say 10 cell with different....how can add these alphabets in one cell i.e. total no. of alphabets in one cell
  • To post as a guest, your comment is unpublished.
    sayad · 1 years ago
    arihhurthj
    hshsjsyshsj
    shshydjdj
    shshssj
    shshdj
    shshjddj

    Please tell me how to add new line with character GO after every line.