Add a suffix to selected cells in Excel
Use this handly VBA macro to automatically add characters to the end of values in a selected range of cells.
I recently encountered a problem where I needed to append some extra digits to the end of a series of cell values (numbers) in Excel. Whilst it is possible for me to manually add two more digits to every cell value, this becomes laborious, especially when dozens or hundreds of cell values are involved. Unfortunately, even an advanced Edit > Replace function cannot append characters to the end of values without deleting existing content, so a Visual Basic for Applications (VBA) script is the only option.
Using the following as an example, I want to add “-14” to the end of each of the numbers in the first three cells:
To do this, a VBA script can be employed. This script will prompt the user to select the range of cells that the user wants to change, then a second prompt will ask for the suffix to be appended. Once that is entered, the highlighted cells will be altered. This script should be added to “ThisWorkbook” in Microsoft Visual Basic for Applications.
Sub AddTextOnRight() Dim Rng As Range Dim WorkRng As Range Dim addStr As String On Error Resume Next xTitleId = "Add A Suffix" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) addStr = Application.InputBox("Add text", xTitleId, "", Type:=2) If addStr = "False" Then Exit Sub For Each Rng In WorkRng Rng.Value = Rng.Value & addStr Next End Sub
Once the macro is executed, the user can either select a range of cells in advance by highlighting, or using the prompt to do so.
The next dialogue box prompts the user to type in the suffix.
The cell values are then changed accordingly.