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

Comments  

Permalink +1 The O
Thanks, Kutools saved me a lot of work :-)
2013-12-20 18:47 Reply Reply with quote Quote
Permalink +2 John Graf
Thank you, the above instructions have saved me hours of work.
2014-01-23 23:24 Reply Reply with quote Quote
Permalink +1 Vishal
CAn i add KUltools without admin rights
2014-01-24 08:02 Reply Reply with quote Quote
Permalink +1 Admin_jay
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.
2014-01-25 02:50 Reply Reply with quote Quote
Permalink +2 Simensch
Thanks!
= CONCATENATE made my day!
2014-03-12 10:58 Reply Reply with quote Quote
Permalink +2 maria
if our prefix was number,how can insert it to cells contain number?
2014-10-24 19:41 Reply Reply with quote Quote
Permalink +2 Ng
maria,
you can edit the code by adding:
Dim OriValue As String

then add this code after For Each....
OriValue = Rng.Value
and change the Rng.Value to OriValue in equation:
Rng.Value = addStr & OriValue
2014-11-21 08:31 Reply Reply with quote Quote
Permalink +2 Ajay
i have a data where in column there are no,s like this
rs 10
rs 20
rs 30
rs 40
Please let me know how can i sum them without removing "rs" from entire column
2015-06-05 18:41 Reply Reply with quote Quote
Permalink +2 Rich
I can't concatenate a date, (excel tries to divide it like a sum).
I have tried changing the format of the source cell and the destination, nothing will stop concatenate from dividing the date.
I don't want to buy Kutools either. (I know there is a free trial but that is not a long term solution).
2015-02-17 11:19 Reply Reply with quote Quote
Permalink 0 Candida
Fantastic site yߋu ɦave Һere Ƅut I was wanting to know iff you knew
of any message boarrds tɦаt cover tthe sane topics discussed in this article?
І'd really loe toօ be a ρart of group wɦere I can ɡet advice fгom other
knowledgeable individuals tnat share tɦe same іnterest.
If you Һave any suggestions, ƿlease lеt me know.
Bless ʏou!

My website :: http://denkmalschutz-immobilien.weebly.com/
2015-03-24 21:54 Reply Reply with quote Quote
Permalink +1 man in
you can also use this site
2015-10-29 04:41 Reply Reply with quote Quote
Permalink -2 I wants to add text
e.g

44444

Out I needed as DL/44444
2015-11-28 12:33 Reply Reply with quote Quote
Permalink 0 Glynis
Thank you so much :)
2016-01-27 10:26 Reply Reply with quote Quote
Permalink +2 Fayyaz
Need Help,
I want to add character in Excell
Example
Cell No A1 Show A so next cell A2 show B
I want to show B in next cell
2016-02-03 06:27 Reply Reply with quote Quote
Permalink +2 sayad
arihhurthj
hshsjsyshsj
shshydjdj
shshssj
shshdj
shshjddj

Please tell me how to add new line with character GO after every line.
2016-02-10 12:46 Reply Reply with quote Quote
Permalink 0 Rajendra
i have single alphabets in different cell, say 10 cell with different....ho w can add these alphabets in one cell i.e. total no. of alphabets in one cell
2016-09-09 13:54 Reply Reply with quote Quote
Permalink 0 Jasvanthi
I have filtered the cloumn and i have to apply TC001 till TC0209 on the filtered cells, could anyone please help me with this
2017-01-06 09:31 Reply Reply with quote Quote

Add comment


Security code
Refresh