EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VALUE Formula in Excel

By Madhuri ThakurMadhuri Thakur

Secondary Sidebar
Excel Functions
  • TEXT and String Functions in Excel
    • Excel CODE Function
    • Excel Find
    • TRIM Formula in Excel
    • Excel CHAR Function
    • LEFT Formula in Excel
    • Opposite of Concatenate in Excel
    • Concatenate Strings in Excel
    • Extract Numbers From String
    • VALUE Formula in Excel
    • Left TRIM in Excel
    • Substring Excel Function
    • SUBSTITUTE Function in Excel
    • Excel VALUE Function
    • Excel REPT Function
    • Excel TRIM Function
    • Excel CLEAN Function
    • Excel EXACT Function
    • PROPER Function in Excel
    • Excel LEFT Function
    • MID Function in Excel
    • LEN Function in Excel
    • CONCATENATE Function in Excel
    • SEARCH Function in Excel
    • RIGHT Function in Excel
    • FIND Function in Excel
    • TEXT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Online COURSE
  • Online Excel Data Analysis Training
Home Excel Excel Resources TEXT and String Functions in Excel VALUE Formula in Excel

value formula in excel

VALUE Formula in Excel (Table of Contents)

  • Excel VALUE Formula
  • How to Use VALUE Formula in Excel?

Excel VALUE Formula

  • Usually, excel recognizes the numeric data type at the time you input the same and stores it as numeric data. Even though if numbers are stored in the text format, Excel recognizes it and changes the data type to numeric. However, there are cases when the numbers are stored in a format unrecognizable to excel. It fails to store those values as numbers and can be left as strings only. In such cases, the excel VALUE function comes to your rescue and does a decent job to convert the values into numbers.
  • VALUE function in excel comes under the category of Text Functions which can be used to convert the numeric strings such as numbers, dates, time, etc., to numbers.

Syntax

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

value formula syntax

An argument in VALUE Formula

text – Is a required argument. That specifies numeric strings such as numbers, dates, time, etc., which have not been recognized by excel as numeric values and hence stored as strings.

How to Use Excel VALUE Formula in Excel?

Excel VALUE Formula is very simple and easy. Let’s understand how to use the Excel VALUE Formula with some examples.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,275 ratings)
You can download this VALUE Formula Excel Template here – VALUE Formula Excel Template

Example #1 – Convert Text into Numeric Value

Suppose you have a value stored as text in cell A2 of the excel sheet as below.

excel Value - Example 1-1

You can recognize value is being stored as text by looking at its alignment. If the value is left-aligned, it is stored as text. Its value is right-aligned; it is stored as a number.

We will see how this string can be converted into a number using the VALUE function.

Start typing VALUE Formula in cell B2.

excel Value - Example 1-2

Give the text string as an argument that you want to be stored as a numeric value. The string is stored in cell A2. Therefore, use A2 as an argument under the VALUE function.

excel Value - Example 1-3

Complete the formula by closing the parentheses and press Enter Key.

excel Value - Example 1-4

You can see that the value in cell B2 is right-aligned. Which means it’s been converted into a number.

Example #2 – Convert Date into Numeric Value

Suppose you have date value stored as a string in cell A2, as shown in the screenshot below.

excel Value - Example 2-1

You can see that the value in cell A2 is stored as a text string through the number pane above. Now, we will see how we can convert this string into a numeric value.

Note: Date values are actually being stored as numbers in the backend of Microsoft Excel. Therefore, you’ll get a numeric date value in excel after you apply the VALUE formula.

Start typing VALUE Formula in cell B2.

excel Value - Example 2-2

Use cell A2 as an input to the cell VALUE function. It is nothing but a text value that you wanted to convert into a numeric value.

excel Value - Example 2-3

Complete the formula by closing the parentheses and press Enter Key.

excel Value - Example 2-4

Here, the value is stored as a sequential number, which is nothing but the distance in terms of days from January 1, 1900. It means that May 30, 2019, is 43615 days after January 1, 1900.

Example #3 – Converting Time stored as Text into Numerical Value

Suppose you have a well-formatted time value in your excel sheet. You can convert it to numeric value as well using the VALUE function.

Suppose you have a well-formatted time value stored in cell A6 of the excel sheet.

Start typing the VALUE formula in cell B2.

excel Value - Example 3-1

The value you need to convert is stored in cell A2. Give A2 as an argument to the VALUE function.

excel Value - Example 3-2

Complete the formula by closing the parentheses and press Enter Key.

excel Value - Example 3-3

You can see the converted time value in cell B2 of the excel sheet. However, you will not frequently use this part of converting date values to a numeric value. But, it is good to have an idea of the same as it is useful to manipulate the time values (like, when you need to subtract the two-time values, this conversion will be helpful).

Example #4 – VALUE Formula to do Mathematical Operations

Like we do with actual numbers, mathematical operations are also possible with the text formatted numerical values using the VALUE formula. Let’s see how we can do this.

Suppose you have two text formatted numeric values as shown in the cells below.

Mathematical Operations 1

Start typing VALUE Formula in Cell B3.

Mathematical Operations 2

Use B1 as an input argument to value function and close the parentheses to complete the formula.

Mathematical Operations 3

Use additional operators, which we use to add two or more numbers after the first VALUE Formula.

Mathematical Operations 4

Use the VALUE formula again after the additional operator and this time give cell B2 as input to it.

Mathematical Operations 5

Press Enter Key to see the output for this expression.

Mathematical Operations 6

Things to Remember About VALUE Formula in Excel

  • Excel VALUE function converts the text string into numeric values
  • It also can be used to convert the formatted text as Date or Time into numeric Values.
  • If the text you are about to convert into a numeric value has not been store explicitly (in any excel cell). You should make sure that it is enclosed in double quotations or single quotations while using an argument under the VALUE function. Because, if not done, it causes #NAME! error in the VALUE formula.
  • You should not use any alphabetical letters that are not convertible into numeric values as an argument to the VALUE function. This formula can convert only those strings to numeric, which are actually numeric but mistakenly stored as texts. If you give alphabets as an argument, the formula will give the #VALUE! error.
  • Microsoft Excel automatically identifies the type of data while you input it. Therefore, the VALUE function is not explicitly required.
  • This function is ideal in situations where you have programs compatible with excel spreadsheet data.
  • It can process any negative or positive numeric value stored as a string.
  • It can convert the fraction values into numeric values, which are actually stored as text by mistake.

Recommended Articles

This is a guide to VALUE Formula in Excel. Here we discuss How to use VALUE Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. LOOKUP Formula in Excel
  2. DAY Formula in Excel
  3. How to Edit Drop Down List in Excel?
  4. Time Difference in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Data Analysis Training (17 Courses, 8+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download VALUE Formula Excel Template

EDUCBA

Download VALUE Formula Excel Template

EDUCBA

डाउनलोड VALUE Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - Excel Training (18 Courses, 9+ Projects) Learn More