In Excel VBA, use the built-in IsNumeric function to check whether a string can be interpreted as a number: If IsNumeric(value) Then. It returns True for strings VBA recognizes as numeric and False for strings it does not; check for blanks, errors, and non-string values separately if those need different treatment.
The Basic VBA Test
IsNumeric is a VBA function, so it belongs in a macro or other VBA procedure rather than in an Excel worksheet formula. A minimal example looks like this:
Sub CheckNumericText()
Dim value As String
value = "42.5"
If IsNumeric(value) Then
MsgBox "This string can be interpreted as a number."
Else
MsgBox "This string is not numeric."
End If
End Sub
In this example, the message reports that the string can be interpreted as a number. Replace the assigned value with a string such as "hello" to see the other result. The test does not change the string into a numeric variable; it only answers whether VBA recognizes its contents as numeric. If you need to do arithmetic afterward, convert the value to a suitable numeric type and handle conversion errors as appropriate.
What IsNumeric Actually Checks
IsNumeric returns True when the entire expression can be recognized as a number, and False when it cannot. This makes it useful for screening user input or text read from a worksheet before attempting a calculation. For example, ordinary numeric text such as "27", "-3.5", and "1.2E3" will generally be recognized as numeric under familiar number conventions. The exact interpretation of separators and symbols can depend on the computer’s regional settings.
The function is not a test for whether a variable was declared with a numeric data type. A String containing "27" may pass, while an actual number stored in a Variant is also numeric. If the distinction matters, check the Variant’s type as well as calling IsNumeric. In particular, a cell that looks like a number may contain either a number or text; its appearance alone does not establish its underlying value type.
Another detail: VBA documentation specifies that a date expression is not numeric for IsNumeric. That differs from an Excel date stored as a serial number in a cell: when VBA reads the underlying numeric cell value, the value can pass a numeric test. Distinguish a date-like text string from a numeric serial value if your application must treat dates specially.
A Safer Reusable Function for Text
If the requirement is specifically “return True only when the supplied value is a nonblank string that VBA recognizes as numeric,” use a small helper function. The checks below prevent error values, Null, Empty, actual numeric values, and whitespace-only strings from being treated as numeric text.
Public Function IsNumericString(ByVal value As Variant) As Boolean
' Return True only for nonblank strings recognized as numeric.
If IsError(value) Then Exit Function
If IsNull(value) Then Exit Function
If IsEmpty(value) Then Exit Function
If VarType(value) <> vbString Then Exit Function
If Len(Trim$(value)) = 0 Then Exit Function
IsNumericString = IsNumeric(value)
End Function
Because a Boolean function defaults to False, each early exit safely rejects values that are not eligible. Trim$ removes ordinary leading and trailing spaces, so a string containing only spaces is rejected. This helper intentionally returns False for an actual numeric value such as a number read from a cell; that is appropriate when the question is whether the original value is text. Use plain IsNumeric instead if actual numbers should also pass.
Checking Values in Worksheet Cells
For data in a worksheet, read the cell value into a Variant so the code can distinguish text, numbers, and error values. This example examines column A from row 2 down and writes a classification in column B. Change the sheet name or columns to match your workbook.
Sub LabelNumericText()
Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range
Dim value As Variant
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
For Each cell In ws.Range("A2:A" & lastRow)
value = cell.Value2
If IsError(value) Then
cell.Offset(0, 1).Value = "Excel error"
ElseIf VarType(value) = vbString Then
If Len(Trim$(value)) = 0 Then
cell.Offset(0, 1).Value = "Blank text"
ElseIf IsNumeric(value) Then
cell.Offset(0, 1).Value = "Numeric text"
Else
cell.Offset(0, 1).Value = "Other text"
End If
ElseIf IsNumeric(value) Then
cell.Offset(0, 1).Value = "Already numeric"
Else
cell.Offset(0, 1).Value = "Other value"
End If
Next cell
End Sub
The separate branches matter. A cell containing a number is labelled “Already numeric,” not “Numeric text”; a text value such as "0042" can be labelled “Numeric text.” Value2 returns the cell’s value without using the VBA Currency or Date subtypes that Value can use in some circumstances. The code leaves the original values in column A unchanged.
Regional Settings, Separators, and Number Formats
Numeric text is interpreted according to VBA and the system’s regional conventions. Decimal and thousands separators are not universally written the same way: one locale may use a period for decimals and a comma for grouping, while another may use the reverse. A string accepted on one computer might not be interpreted identically on another. Currency symbols and other formatting characters can also affect whether a string passes, so do not assume that every string that looks numeric in a particular display format will pass everywhere.
Cell formatting is a separate issue. Applying a number format to a text value does not necessarily convert that value into a number. Conversely, a cell holding a numeric value may display commas, a currency symbol, or a date because of its number format. When validating imported data, consider both the stored value and the expected input convention. If the workbook will be used across regions, define the accepted format explicitly instead of relying on a test whose interpretation may vary.
IsNumeric Compared with Val and Conversion
IsNumeric is a validation check; Val is a conversion function with different behavior. Val reads a numeric portion from the beginning of a string and stops when it encounters a character it does not recognize. As a result, a string such as "12abc" can produce a numeric portion of 12 with Val, even though the entire string is not a valid number. That makes Val a poor substitute when the goal is to reject malformed input.
After validation, use a conversion such as CDbl when a Double is appropriate, or another conversion that matches the range and precision you need. Conversion can still raise an error if the value cannot be converted, and it follows applicable locale conventions. Do not use validation alone as proof that a value fits a particular business rule: a numeric string may still be too large, negative when only positive values are allowed, or outside an accepted decimal range.
When You Need Stricter Validation
IsNumeric answers a broad question: can VBA interpret this value as numeric? It does not enforce a custom grammar. For example, a program that accepts only whole-number IDs may need to reject decimal notation, signs, exponent notation, or leading and trailing spaces even if VBA recognizes some of those forms. Likewise, a postal code or account number that contains only digits may need to remain text so leading zeros are preserved.
Before choosing a stricter test, write down the permitted format. Useful decisions include:
- Whether a sign is allowed and whether negative values are valid.
- Whether decimals or scientific notation are permitted.
- Which decimal and grouping separators users may enter.
- Whether spaces, currency symbols, or percent signs are allowed.
- Whether the value is a quantity or an identifier that must retain leading zeros.
For a fixed format, validate each character or use a carefully specified pattern rather than assuming IsNumeric is a complete business-rule check. If the input should represent an integer, also verify that the converted value has no fractional part and falls within the required range. Keep identifiers as strings when their digits are labels rather than quantities.
Common Problems and Safe Macro Use
A frequent source of confusion is testing a cell’s displayed text rather than its stored value. A formula result, a number formatted with decimals, and a text entry that resembles a number can look alike while having different underlying types. Another common issue is passing an Excel error value such as #N/A directly into later conversion logic. Check IsError first, as in the worksheet example, before doing operations that assume ordinary text or numbers.
To add or run VBA on Windows, press Alt+F11 to open the Visual Basic Editor; on Mac, use Option+F11 (some keyboards also require Fn). Save the workbook as a macro-enabled workbook, such as .xlsm, if it needs to retain its VBA. Enable macros only for files you trust. Save a separate copy before running a macro that writes to cells: macro actions cannot be undone with Ctrl+Z, and the sample writes labels into column B.
Frequently Asked Questions
Does IsNumeric work on a string variable?
Yes. A String variable containing a value VBA recognizes as a number can return True. The function also accepts other expression types, which is why a Variant and an explicit type check are useful when you need to test text only.
Does IsNumeric accept negative numbers and decimals?
It generally recognizes ordinary negative and decimal numeric strings, subject to regional conventions. If your rule allows only integers, positive amounts, or a defined number of decimal places, add those checks separately.
Why does IsNumeric return False for a date?
VBA treats a date expression as a date rather than a numeric expression for this test. A worksheet date stored as a serial number is a different case: the underlying cell value may be numeric even though its displayed format looks like a date.
Can I use IsNumeric in an Excel cell formula?
No. IsNumeric is a VBA function, not a worksheet function. Excel’s ISNUMBER worksheet function checks whether a value is a number, but text that merely looks numeric is still text unless it is converted.
Should I use IsNumeric or Val?
Use IsNumeric to check whether the whole value is recognized as numeric. Val extracts a numeric portion from the start of a string and may ignore trailing characters, so it is not a reliable whole-string validation test.
Related
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Recorder: Is It Available Without a License?Excel’s Macro Recorder is built into the desktop app; it is not a separate add-on that needs its…
- VBA in Excel: Open the Editor with a Keyboard ShortcutTo open the VBA editor in Excel for Microsoft 365 on Windows, press Alt+F11. This opens the Visual…
- Excel UserForm Controls: A Practical Guide to Types and UseExcel UserForm controls are the buttons, text boxes, lists, and other interface elements you add to a VBA…
- VBA Macro Excel Workbook: Enable Editing on Open SafelyNo: a VBA macro cannot safely or legitimately click Excel’s Enable Editing button in Protected View. To edit…
- Keyboard Recorder Macro: How It Works and When to Use OneA keyboard recorder macro is a tool that captures a sequence of keystrokes—and sometimes mouse actions or delays—so…
- VBA Find Function: Examples, Options, and Common FixesIn Excel VBA, use the Range.Find method to search a range for a value or text. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.