How to add prefix or suffix to range of cells in Excel?
Adding a prefix or suffix to a range of cells in Excel is a common requirement when preparing data for reports, enhancing readability, or formatting information consistently. Whether you need to append units to numbers, add identifiers to product codes, or simply format names and other text, this guide will equip you with the necessary tools. We'll cover three effective methods: using formulas, employing VBA scripts, and utilizing Kutools for Excel, each providing a practical solution depending on your proficiency and the complexity of your task.
- Add prefix or suffix to all cells with formulas
- Add prefix or suffix to all cells with Kutools for Excel
- Add prefix or suffix to all cells with VBA
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 |
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 - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!
1. Select the cells that you want to add prefix or suffix, and click Kutools > Text > Add Text.
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:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now!
Add 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:
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:
Effectively adding prefixes or suffixes to cell data can streamline data presentation and ensure consistency across your Excel documents. Whether you chose to use formulas for simplicity, VBA for automation, or Kutools for Excel for its enhanced functionality and user-friendliness, each method offers a reliable way to modify cell contents swiftly. By applying these techniques, you can improve the clarity and utility of your data, making your spreadsheets more functional and easier to understand. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.
Demo: add prefix or suffix to multiple cells with Kutools for Excel
Related articles
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
![Screen Shot of Excel (with Office Tab installed)](http://cdn.extendoffice.com/images/stories/kte-module/officetab-bottom-02.gif)
![Sawan](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![Somu](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![Amanda Lee](http://cdn.extendoffice.com/images/discuss_avatar/41841_3.jpg)
![dddd](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![Pboro](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![Vasudeva](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![daniyal ejaz](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)