Search and replace text in cell with single character

This should do it:

Option Explicit

Sub ReplaceAllSpecial()
Dim RNG As Range
Dim MySearch As String
Dim MyReplace As String
Dim sFIND As Range
Dim sFIRST As Range


Set RNG = Application.InputBox("Click on the column to search", "Choose column(s)", "A:A", Type:=8)
If RNG Is Nothing Then Exit Sub

MySearch = Application.InputBox("What string to search for?", "Search", "cat", Type:=2)
If MySearch = "False" Then Exit Sub

MyReplace = Application.InputBox("Enter the replacement string", "Replace", "1", Type:=2)
If MyReplace = "False" Then Exit Sub

Set sFIND = RNG.Find(MySearch, LookIn:=xlValues, LookAt:=xlPart)
If Not sFIND Is Nothing Then
    Set sFIRST = sFIND
    Do
        On Error GoTo Finish
        sFIND = MyReplace
        Set sFIND = RNG.FindNext(sFIND)
    Loop Until sFIND.Address = sFIRST.Address
Else
    MsgBox "Search string '" & MySearch & "' was not found."
End If

Finish:
End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select ReplaceAllSpecial from the macro list.