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
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|
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:
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.
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:
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Spreadsheets: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
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!