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.

Comments

2 responses to “Add a suffix to selected cells in Excel”

On 26 April 2017, Avinash riwary wrote:

Thank you!

Reply

On 10 January 2020, Mohith wrote:

Hello,

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.

Example:
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.

Thanks,
Mohith

Reply

Have Your Say

Δ