Higman
Assuming you want to do this manually:
A neat way would be to define a name called COMMA and give it a value =","
Then, assuming your data is in the range A1 to A100, enter the formula =A1 &
COMMA into cell B1 and then copy and paste, or drag and fill, this through
cells B2 to B100. Any formulae or mail merges etc could then be directed to
use the modified data in range B1 to B100
To do this using VBA code (Excel 2003):
Add the following procedure to a VBA module, highlight the cells you wish to
append the comma to and run the procedure. If this were going to be run on a
regular basis I suggest you assign a shortcut key to the macro to speed
things up.
Option Explicit
Sub Append_Character()
Dim rng As Range
Dim rngCell As Range
Const sCHARACTER As String = "ABC"
On Error GoTo Exit_Append_Character
Set rng = ActiveWindow.RangeSelection
' *** a) Append sCHARACTER to all selected cells
' For Each rngCell In rng.Cells
' rngCell.Value = rngCell.Value & sCHARACTER
' Next rngCell
' *** b) Only append sCHARACTER to non blank cells that do not already end
in sCHARACTER
For Each rngCell In rng.Cells
'Skip blank cells
If IsEmpty(rngCell.Value) = False Then
'Skip cells already ending in character
If Mid(rngCell.Value, Len(rngCell.Value), Len(sCHARACTER))
<> sCHARACTER Then
rngCell.Value = rngCell.Value & sCHARACTER
End If
End If
Next rngCell
Exit_Append_Character:
Set rngCell = Nothing
Set rng = Nothing
End Sub
I have suggested two possible choices for determining when to append the
character. There are no doubt countless others but without knowing the
reason for appending the character this is the best I can do in the
circumstances.
I hope this helps.
Kind regards
Charles Wilcockson