Skip to content

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:

Excel sheet with cells containing random number strings

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.

Dialogue box requesting entry of cell range

The next dialogue box prompts the user to type in the suffix.

Dialogue box requesting suffix text

The cell values are then changed accordingly.

Excel cells with altered values

   

Comments:

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

  • Written by Avinash riwary on 26 April 2017:

    Thank you!

  • Written by Mohith on 10 January 2020:

    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

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.