• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VBA Val

Home » VBA » VBA Conversion Functions » VBA Val

By Sharmila Reddy Leave a Comment

VBA VAL

Val Function in Excel VBA

VAL stands for VALUE in VBA terminology. VBA VAL function converts the string containing number to an actual number. For example, if you supply the text string “1234 Global” it will return only the numerical part i.e. 1234.

Often times, when we download or get the data from web numbers, are usually stored as text values in a spreadsheet. Conversion of text to numbers is the hardest task if you are not aware of the correct function in excel. As a regular worksheet function, we have a function called VALUE which will convert all the string that represents numbers to exact numbers with a simple function in the worksheet. In this article, we will show how this can be achieved in VBA using the VAL function.

Syntax of VAL Function in VBA

Now take a look at the syntax of VAL function in VBA. It has only one argument i.e. String.

VBA Val Formula

String: It is simply a string value we are trying to get the numerical part out of it.

So, the VAL function converts the supplied string to a numerical value.

Note: VAL function always ignores the space characters and continues to read the numbers after the space character or characters.

For example, if the supplied string is “145  45    666 3” it will ignore the space characters and return the result as “145456663”.

Popular Course in this category
Cyber Monday Sale
VBA Training (3 Courses, 12+ Projects) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
4.6 (247 ratings)
Course Price

View Course

Related Courses

Examples of VAL Function in Excel VBA

Below are the examples of VBA Val Function in Excel.

You can download this VBA Val Function Excel Template here – VBA Val Function Excel Template

Example #1

Let’s try the first example with simple number i.e. “14 56 47”

Below code is for you.

Code:

Sub Val_Example1()

   Dim k As Variant

   k = Val("14 56 47")
   'Convert the above as 145647

   MsgBox k

End Sub

VBA Val Example 1

When you run VBA code using F5 key or manually then, it will return the result as “145647” by ignoring all the space characters as shown in the below image.

VBA Val Example 1-1

Example #2

In this example, we will see what the result of the string is “+456”.

Code:

Sub Val_Example2()

   Dim k As Variant

   k = Val("+456")
  'Convert the above as 456

   MsgBox k

End Sub

Example 2

You can run this code manually or through F5 key to return the value as 456 by ignoring +456.

Example 2-1

Example #3

Now let try the same number with a negative sign.

Code:

Sub Val_Example3()

   Dim k As Variant

   k = Val("-456")
  'Convert the above as -456

   MsgBox k

End Sub

VBA Val Example 3

This code will return the value as -456 only because number with operator sign should be shown.

VBA Val Example 3-1

Example #4

Now let try this string “100 Kg”.

Code:

Sub Val_Example4()

    Dim k As Variant

    k = Val("100 KG")
    'Ignores KG and returns only 100

    MsgBox k

End Sub

Example 4

If you run this code manually or using F5 key then this above code ignores “KG” and returns only “100” in the VBA message box.

VBA Val Example 4-1

Example #5

Now try the date string i.e. “14-05-2018”.

Code:

Sub Val_Example5()

   Dim k As Variant

   k = Val("14-05-2019")
  'Returns 14 as the result.

   MsgBox k

End Sub

VBA Val Example 5

The above code returns 14 as the result because VBA VAL function can only fetch the numerical value until it finds any other than numerical character.

VBA Val Example 5-1

Example #6

Now try the string “7459Good456”.

Code:

Sub Val_Example6()

    Dim k As Variant

    k = Val("7459 Good 456")
    'Returns 7459 as the result.

    MsgBox k

End Sub

Example 6

This will extract the numbers until it finds the non-numerical character i.e. result is 7459. Even though there are numerical values after the non-numerical value “Good”, it completely ignores numbers after that.

VBA Val Example 6-1

Example #7

Now try the string value “H 12456”.

Code:

Sub Val_Example7()

    Dim k As Variant

    k = Val("H 12456")
    'Returns 0 as the result.

    MsgBox k
 
End Sub

Example 7

Run the above code using shortcut key F5 or manually then it returns the result as zero. Because the very first character of the string we have supplied is a non-numerical character so, the result is zero.

VBA Val Example 7-1

Example #8

Now try this string “24545    .   2”.

Code:

Sub Val_Example8()

    Dim k As Variant

    k = Val("24545 . 2")
    'Returns 24545.2 as the result.

    MsgBox k

End Sub

Example 8

The code returns the result as 24545.2 because VBA VAL function considers the character dot (.) as the decimal character and returns the result accordingly.

VBA Val Example 8-1

Recommended Articles

This has been a guide to VBA Val Function. Here we learn how to use VAL Function in VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • VBA Regular Expression
  • Examples to Change Font Color in VBA
  • How to use CSTR in VBA?
  • VBA Like Operator Examples
  • Sleep Function in VBA
  • VBA Pivot Table
  • VBA Count Function
  • Use VBA AutoFill
  • Create Random Numbers in VBA
  • Apply Break Points in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: VBA, VBA Conversion Functions

Reader Interactions
Leave a Reply Cancel reply

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

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download VBA Val Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Limited Period Offer - VBA Training Course (6 courses, 35+ hours video) View More