Add Leading Or Trailing Text To Constant Cells In A Range

You will be prompted with an input-box for the range of cells and for the leading text.

'==================================================================================================
' ## Add leading text to all the cells in a selected range
'    You will be prompted with a input box for the range of cells
'    and for the leading text.
'==================================================================================================
Sub LeadingText()
    '// Vars
    Dim rngCell         As Range
    Dim rngSelection    As Range
    Dim rngInitial      As Range
    Dim strLeading      As Variant
    Dim strPrompt       As String

    '// Test a range selected
    If TypeName(Selection) <> "Range" Then Exit Sub

    '// Prompt the user to select a range
    strPrompt = "Select the range to add leading text" & vbDoubleLine & _
                "Click cancel to quit this task"

    On Error Resume Next
        Set rngInitial = Application.Selection
        Set rngSelection = Application.InputBox(strPrompt, "Add Leading Text to Cell Values", rngInitial.Address, Type:=8)
    On Error GoTo 0

    '// Check if user clicked cancel
    If rngSelection Is Nothing Then Exit Sub

    '// Constants only
    Set rngSelection = Intersect(rngSelection, _
                       rngSelection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))

    '// Enter the separator between each cell value, check if user clicked cancel
    strLeading = Application.InputBox(Prompt:="Enter the leading text, don't forget the space", _
                                        Title:="Add Leading Text to Cell Values", Type:=2)

    '// If User clicked cancel then exit sub
    If strLeading = False Then Exit Sub

    '// Screen updating off, Add leading text then back on
    Application.ScreenUpdating = False

    For Each rngCell In rngSelection
        If rngCell.Value <> "" Then rngCell.Value = strLeading & rngCell.Value
    Next rngCell

    Application.ScreenUpdating = True
End Sub

'==================================================================================================
' ## Add trailing text to all the cells in a selected range
'    You will be prompted with a inputbox for the range of cells
'    and for the trailing text.
'==================================================================================================
Sub TrailingText()
    '// Vars
    Dim rngCell         As Range
    Dim rngSelection    As Range
    Dim rngInitial      As Range
    Dim strTrailing      As Variant
    Dim strPrompt       As String

    '// Test a range selected
    If TypeName(Selection) <> "Range" Then Exit Sub

    '// Prompt the user to select a range
    strPrompt = "Select the range to add trailing text" & vbDoubleLine & _
                "Click cancel to quit this task"

    On Error Resume Next
        Set rngInitial = Application.Selection
        Set rngSelection = Application.InputBox(strPrompt, "Add Trailing Text to Cell Values", rngInitial.Address, Type:=8)
    On Error GoTo 0

    '// Check if user clicked cancel
    If rngSelection Is Nothing Then Exit Sub

    '// Constants only
    Set rngSelection = Intersect(rngSelection, _
                       rngSelection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))

    '// Enter the separator between each cell value, check if user clicked cancel
    strTrailing = Application.InputBox(Prompt:="Enter the trailing text, don't forget the space", _
                                        Title:="Add Trailing Text to Cell Values", Type:=2)

    '// If User clicked cancel then exit sub
    If strTrailing = False Then Exit Sub

    '// Screen updating off, Add trailing text then back on
    Application.ScreenUpdating = False

    For Each rngCell In rngSelection
        If rngCell.Value <> "" Then rngCell.Value = rngCell.Value & strTrailing
    Next rngCell

    Application.ScreenUpdating = True
End Sub