How to check if a Cell in an Excel Worksheet has numbers using VBA

Excel VBA provides many in-built functions, which helps programmers accomplish a task. I am creating a simple scenario to use one of the in-built functions called IsNumeric() to check if a particular cell in my Excel Worksheet has numbers.

Syntax for IsNumeric() Function

IsNumeric( expression )

The IsNumeric() function (like Excel ISNUMBER()) returns a Boolean value, that is, true or false. It takes a parameter, a variant value for evaluation.

A cell in an Excel worksheet can have a variety of data in the form of text, numbers, formulas etc. If you are a VBA programmer, you can simply use the function “IsNumeric()” to check if the cell has a number. However, a cell may have “alphanumeric” values too and if that is the case, then I want to find and extract “only the numbers” from string and show it in another cell. Please see the image below.

Check if Cell has Number in Excel Worksheet using VBA IsNumeric() Function

The VBA Code

Press Alt+F11 to open VBA and click Project Explorer from the tool bar. In the Project Explorer window, expand Microsoft Excel Object and double Sheet1. That’s where I have my data. Now write the below code.

Option Explicit

Private Sub CommandButton1_Click()
    Worksheets("Sheet1").Range("B2:B10").ClearContents      ' CLEAR ALL THE VALUES.
    checkNumber (Worksheets("Sheet1").Range("A2:A10"))
End Sub

Sub checkNumber(objRange As Range)

    Dim myAccessary As Variant
    Dim i As Long
    Dim iRow As Long
   
    iRow = 2
   
    For Each myAccessary In objRange        ' LOOP THROUGH ELEMENTS OR VALUES IN EACH ROW.
        For i = 1 To Len(myAccessary.value) ' LOOP THROUGH EACH CHARACTER OF THE STRING (CELL VALUE).
            ' CHECK IF THE CHARACTER IS A NUMBER.
            If IsNumeric(Mid(myAccessary.value, i, 1)) Then
               
                ' SHOW THE NUMBER IN THE 2ND COLUMN OF THE SAME ROW.
                If Trim(objRange.Cells(objRange.Row - 1, 2)) <> "" Then
                    objRange.Cells(iRow - 1, 2) = _
                        objRange.Cells(iRow - 1, 2) & Mid(myAccessary.Text, i, 1)
                Else
                    objRange.Cells(iRow - 1, 2) = Mid(myAccessary.Text, i, 1)
                End If
                    
            End If
        Next i
        iRow = iRow + 1
    Next myAccessary
End Sub

Also Read: How to Find and Highlight Duplicate Values in Excel using VBA

I have two For loops. The first loop gets the element (cell values in every Row) and the second loop extracts each character in the cell values, using the function Mid().

Mid(myAccessary.value, i, 1)

Next, I have a condition that checks if the character extracted using the function Mid() is a number or not. The IsNumeric() function evaluates the character. When it finds the number, it writes it in the next cell (Column Quantity) of the same row. If there are more than one numbers in the cell, I’ll concatenate the values and show it.

In this way I’ll check and extract the numbers from a given string and get the Quantity in Pcs.

Well, that’s it. Thanks for reading. ☺

← PreviousNext →

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

How to check if a Cell in an Excel Worksheet has numbers using VBA

Connect With Me

Tools you can use

Syntax for IsNumeric() Function