Val VBA function – How to extract a number from a text

1. Val VBA function – Description

The Val VBA function   searches for occurrences of numbers in a string. If a number appears at the beginning of a string, the function returns its value in the form of a number with the Double data type. When a string starts with non-numeric characters, the Val function returns zero.

2. VBA Val Function – Syntax

'datatalk.pl
Val (String As String) As Double

String : Any numeric value in the numeric data type.

Function return : Double / numeric value.

3. VBA Val function – Example

How to use Val function   in VBA Excel? Below are examples of using the Val function   in the VisualBasic Editor.

Example 1:  Extracting numbers from text. Val – an example of a VBA function. The result in the MsgBox window.

Dim StrValue As String
StrValue = "100 times 100"
MsgBox Val (StrValue)
MsgBox Val ("12 23")

Example 2:  The occurrence of numbers in the text. VBA Val – an example of a function. The result in a sheet cell.

Dim StrValue2 As String
StrValue2 = "100 times 100"
Range ("A1") = Val (StrValue2)
Range ("A2") = Val ("12 23")
Excel VBA functions - Val VBA function
Excel VBA functions – Val VBA function

4. VB Val function – Additional information

  • The function ignores spaces between numbers, e.g. for saved telephone numbers.
  • The games function encounters non-numeric characters, returns only and only the numeric characters before the text.

5. Val VisualBasic function – Where to use?

The function can be used in: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016.

The article is part of the VBA Excel function list. You can find a list of all VBA functions at this address:  VBA functions .

Leave a comment

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

%d bloggers like this: