Example. A1 contains date in format mm/dd/yy (let's say 01/31/18)
I want A2 to show "C013118".
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.
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.
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:
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful: