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.
2 responses to “Add a suffix to selected cells in Excel”
Could you please help me on this?
I need to add a suffix of numbers to a number in a particular cell and the sequence should be for a quantity specified in another cell.
Cell A1 contains number – 12345
Cell B1 contains number – 5
Suffix sequence – 01, 02 ~~~ until the count reaches B1 cell quantity
In another cells I need sequence numbers as 1234501, 1234502, 1234503, 1234504, 1234505
Request your kind help.