Replacing only digits inside a range of cells in an excel

Question

I have an excel column with cell values as string mixed with some numbers ar the end. I am not able to do a 'Text to column' as space cannot be a delimiter nor tab.I tried the below code but it works only if the entire cell is a only digits I am a beginner in macros and vb

Sub ReplaceNoX()
 Dim cell As Object
 Dim val As String
 Dim i As Integer
 Dim n As String
 Application.ScreenUpdating = False
 For Each cell In Selection
 If IsNumeric(cell.Value) Then
 val = cell.Text
 For i = 1 To Len(val)
 n = Mid(val, i, 1)
 If "0" <= n And n <= "9" Then
 Mid(val, i, 1) = "x"
 End If
 Nextf
 cell.Formula = val
 End If
 Next
 Application.ScreenUpdating = True
 End Sub

Answer 1

If you want to change both numbers and mixed text and numbers, then remove the IsNumeric() test:

Sub ReplaceNoX()
    Dim cell As Range
    Dim val As String
    Dim i As Long
    Dim n As String

    Application.ScreenUpdating = False
        For Each cell In Selection
            val = cell.Text
            For i = 1 To Len(val)
                n = Mid(val, i, 1)
                If n Like "[0-9]" Then
                    Mid(val, i, 1) = "x"
                End If
            Next i
            cell.Value = val
        Next
    Application.ScreenUpdating = True
End Sub

NOTE:

1. use Long rather than Integer
2. use Like to check individual characters.
3. use Range rather than Object

Answer 2

If you do want to replace every digit with an 'x', here is a different approach using regular expressions.

Sub Regex1()

Dim oRgx As Object, rCell As Range

Set oRgx = CreateObject("VBScript.RegExp")

With oRgx
    .Global = True
    .Pattern = "\d"
    For Each rCell In Selection
        rCell.Value = .Replace(rCell, "x")
    Next rCell
End With

End Sub