How to replace all numbers in cells with X’s in Excel?
As below screenshot shown, how to replace all numbers in cells with x’s in Excel? The method in this article will help you solving the problem.
Recommended Excel Productivity Tools
The following VBA code can help you replacing all numbers in selected cells with X’s in Excel. Please do as follows.
1. Select the cells containing numbers you will replace with x, and then press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the Code window.
VBA code: Replace all numbers in selected range with x’s in Excel
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 Next cell.Formula = val End If Next Application.ScreenUpdating = True End Sub
3. Press the F5 key to run the code. Then you can see all numbers in selected cells are replaced with x as below screenshot shown.
- How to find and replace all blank cells with certain number or text in Excel?
- How to replace commas with newlines (Alt + Enter) in cells in Excel?
- How to replace line breaks with br in Excel?