Removing Non numeric characters from cell in Microsoft Excel using VBA

Your 2020 Plan. Learn 20 minutes a day for 20 days. Online courses from €9.99.

This blog will demonstrate how to remove the non numeric characters from the cell in Microsoft Excel 2016.

Assume that you have a worksheet where a cells from the range “A1:D30” contains the alpha-numeric characters like ABHGFD458909LOGH and you have a task to retain only the numeric characters in the cell and remove the non-numeric characters.

How can you remove the non-numeric characters from a cell in Microsoft Excel ?

One of the options we have is to use the the VBA code to remove the non-numeric characters.

1. Open the Microsoft Visual Basic for Application Window using the shortcut key “ALT + F11”.

2. In the Microsoft Visual Basic for Application Window , select the menu Insert –> Module.

image

3. Paste the following code in the Module window and press F5 key to run it.

Sub RemoveNonNumeric()
    Dim sheetName As String
    sheetName = "Sheet1"
    Dim CellRange As String
    CellRange = "A1:D30"
    Dim rng As Range
    For Each rng In Sheets(sheetName).Range(CellRange).Cells
     Dim index As Integer
     Dim result As String
     result = ""
        For index = 1 To Len(rng.Value)
            Select Case Asc(Mid(rng.Value, index, 1))
                Case 48 To 57:
                    result = result & Mid(rng.Value, index, 1)
                End Select
        Next
        rng.Value = result
    Next
End Sub

You should immediately see only the numeric characters in the cell…

Leave a Reply

Your email address will not be published. Required fields are marked *