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.
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?
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 5 months agoI have tried using the verbal basic module described above but received the following error: Compile error: Invalid outside procedureI am not a programmer and am not well versed in verbal basic. Any suggestions on how to resolve this error? I copied and pasted what is on this website, including the numbers 1-20, and get this error. I tried without the line numbers, and did not get the error but not all of the cells in the range had the numbers replaced with x's.
- To post as a guest, your comment is unpublished.· 3 years agoThanks for this! Really useful