Open the VB editor (Alt F11). Menu Insert - Module. Paste the following:
Code:
Sub AddText()
Dim RNG As Range, Cel As Range
Dim S1 As String, S2 As String
On Error Resume Next
Set RNG = Application.InputBox ("Select the cells to have additional text:", _
"1 of 3", _
Selection.Address (True, True, Application.ReferenceStyle), _
Type: = 8)
On Error GoTo 0
If RNG Is Nothing Then Exit Sub
Set RNG = Intersect (RNG, ActiveSheet.UsedRange)
S1 = InputBox ("Text before the cell value:", "2 of 3")
If StrPtr (S1) = 0 Then Exit Sub 'Cancel
S2 = InputBox ("Text after the cell value:", "3 of 3")
If StrPtr (S2) = 0 Then Exit Sub
For Each Cel In RNG
If Cel.Value <> "" Then 'not empty?
If Cel.HasFormula = False Then 'not formula?
Cel.Value = S1 & S2 & Cel.Value
End If
End If
Next
End Sub
In real life I would have made a simple user form instead of the three dialogues, but it is a little more high.
Bookmarks