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.
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
In the button’s (an ActiveX control) click event, I am calling a procedure called checkNumber(). The procedure takes a parameter in the form of a Range. Therefore, I am passing the Range (“A2:A10”), where I have my data with cells that might have numbers.
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.