VALUE in Excel: how to use it
VALUE turns a number stored as text back into a real number, so you can calculate with it.
Try the first exercises for freeIn the lesson In practice: cleaning up data
What does VALUE do?
VALUE turns text that looks like a number back into a real number. "125" as text becomes 125 as a number, and you can calculate with it, add it up and compare it.
Numbers stored as text often come from another program or a website, or appear when you cut digits out of a code with LEFT, RIGHT or MID. You can spot them because they're on the left of the cell and SUM doesn't count them.
When do you use VALUE?
- Digits you cut out of a code: =VALUE(RIGHT(A2,4)).
- A weight with a unit after it: =VALUE(SUBSTITUTE(A2," kg","")).
- A column of numbers from an export that SUM doesn't count: make a helper column with =VALUE(A2).
- Comparing a code stored as text with a number: =VALUE(A2)=B2.
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =VALUE(RIGHT(A2,4))
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
VALUE | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | VALUE |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
text | A cell or text that looks like a number, such as 0125 from a code. | RIGHT(A2,4) |
VALUE step by step
C1 contains =VALUE(RIGHT(A2,4)). RIGHT gives the text 0125. VALUE turns it into the number 125. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Start with what gives the text: RIGHT takes the last four characters from the code.
=RIGHT(A2,4) - Feel free to press Enter for a moment: you see 0125, on the left of the cell, because it's text. Then click back into the formula.
- Put VALUE around it: after the equals sign, type VALUE and a bracket, and close that bracket at the end.
=VALUE(RIGHT(A2,4)) - Press Enter: you get the number 125, on the right of the cell.
More examples
Removing a unit
C1 contains =VALUE(SUBSTITUTE(A2," kg","")). First SUBSTITUTE removes the unit, then VALUE turns it into the number 12.5.
Adding up what's stored as text
C1 contains =VALUE(A2)+VALUE(A3)+VALUE(A4). The three quantities are stored as text, as if they came from an export. =SUM(A2:A4) would give 0, but with VALUE you can still add them up.
A date as text
C1 contains =VALUE(A2)+30. VALUE also turns a date stored as text back into a real date. Then you can calculate with it: 30 days later.
How to spot numbers stored as text
They sit on the left of the cell instead of the right, there's a green triangle in the corner, and SUM doesn't count them. It often happens with data from another program or a website.
Arithmetic works too
A calculation such as =RIGHT(A2,4)*1 also turns the text into a number. VALUE says more clearly what you mean, which helps whoever reads your file later.
Where it often goes wrong
- If the text contains a unit, such as "12 kg", VALUE gives #VALUE!. Remove it first with SUBSTITUTE.
- If the text uses a different decimal separator from your Excel, replace it first with SUBSTITUTE.
Practise this in the lesson In practice: cleaning up data. The first exercises are free.
Frequently asked questions
How do I turn a whole column of text into numbers?
Select the column and choose Data > Text to Columns > Finish. Or make a helper column with VALUE and paste the result back as values.
Read next
LEFT, RIGHT and MIDTRIMLENUPPER, LOWER and PROPERSEARCH and FIND