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?
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!