adding text /macro to insert text in cell

  • Thread starter Thread starter ss
  • Start date Start date
S

ss

I have a list of phone and fax numbers and I want to put
in (p) and (f) next to these numbers within the cell- is
there a quick way of doing this? If I run/ record a macro,
how do I do this?
 
ss

To start with, you don't need a macro. You can use a helper column and enter

=A1&"(p)"

Drag/copy down as far as you need.

If you want a macro.......

FIRST!! Make a copy of your workbook to play with.

Hit ALT + F11 keys to open the Visual Basic Editor.

View>Project Explorer.

Find your workbook/project and right-click on it.

From fly-out menu select "Insert>Module.

Copy/paste the Add_Text code in there.

Sub Add_Text()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo Endit
whichside = InputBox("Left = 1 or Right =2")
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
If whichside = 1 Then
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Else
For Each cell In thisrng
cell.Value = cell.Value & moretext
Next
End If
Exit Sub
Endit:
MsgBox "only formulas in range"
End Sub

Hit ALT + Q to go back to Excel Window.

Select your range of cells to alter then Tools>Macro>Macros. Select Add_Text
and "Run".

Gord Dibben Excel MVP
 
Back
Top