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.
Replace all numbers in cells with x’s with VBA code
Replace all numbers in cells with x’s with VBA code
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 Sub3. 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.
Related articles:
- 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
