The VALUE function converts a piece of text that looks like a number into an actual number that Excel can calculate with. It is the quick fix when imported or copied values are stored as text and refuse to add up.
=VALUE(text)What does VALUE do?
The VALUE function takes text that represents a number and returns the real number behind it. For example, =VALUE("1,200") returns 1200, and =VALUE("15%") returns 0.15. Excel stores the result as a number, so you can add it, multiply it, compare it or chart it straight away.
This matters because Excel treats text and numbers differently. A cell that looks like 1,200 but is stored as text will be ignored by SUM, sorted incorrectly and flagged by error checks. This often happens with data pasted from websites, exported from accounting systems or typed with stray spaces and currency symbols.
Imagine downloading a monthly sales report where every figure sits in a text column. Totals show zero and charts stay empty. Wrapping the values in VALUE converts them in one step, so your sums, averages and dashboards work again without retyping anything.
When to use VALUE
- Converting prices or amounts imported as text, such as "$1,200" or "1.200,50".
- Turning percentage text like "15%" into 0.15 for calculations.
- Converting date text such as "2024-03-15" into a real date serial number.
- Cleaning numbers that contain spaces, apostrophes or non-breaking characters.
- Preparing text-based IDs or codes for arithmetic or lookups.
- Feeding text numbers into formulas that require numeric arguments.
VALUE syntax
=VALUE(text)| Argument | Required? | What to enter |
|---|---|---|
text | Required | The text you want to convert to a number. It can be a text string in quotes, a cell reference, or a value returned by another function. Example: =VALUE("1,200") or =VALUE(A2). |
What it returns: A number. If the text is a valid number, date, time or percentage in a recognised format, VALUE returns the numeric equivalent. If the text cannot be interpreted as a number, VALUE returns the #VALUE! error.
VALUE examples
1 Convert a simple text number
This example shows the most basic case: a number stored as text. The goal is to turn the text in B2 into a real number. =VALUE(B2) reads the characters "1250" and returns the number 1250, which can then be used in any calculation. The result is right-aligned by default, confirming it is now numeric.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Text Value | |||
| 2 | Notebook | 1250 | 1250 | ||
| 3 | Pen | 340 | |||
| 4 | Folder | 875 | |||
| 5 |
2 Convert a price with a currency symbol
Prices copied from a website often include a dollar sign and thousands separator. The goal is to get the numeric amount from B2. =VALUE(B2) understands the currency format and returns 1200. The same formula works for B3 and B4, returning 850 and 2400. Once converted, the values can be summed or averaged normally.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price Text | |||
| 2 | Laptop | $1,200 | 1200 | ||
| 3 | Monitor | $850 | |||
| 4 | Keyboard | $2,400 | |||
| 5 |
3 Turn percentage text into a decimal
Percentage values exported as text will not multiply correctly. The goal is to convert "15%" in B2 into 0.15. =VALUE(B2) interprets the percent sign and returns the decimal 0.15. Multiply that by a base amount to calculate the actual discount or tax. The other rows convert in the same way.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Rate Text | |||
| 2 | Standard | 15% | 0.15 | ||
| 3 | Reduced | 7.5% | |||
| 4 | Zero | 0% | |||
| 5 |
4 Convert date text to a real date number
Dates stored as text cannot be sorted chronologically or used in date arithmetic. The goal is to convert the text date in B2 into Excel's date serial number. =VALUE(B2) reads "2024-03-15" and returns 45366, the serial number for 15 March 2024. Format the result as a date to display it normally. The other rows convert to their own serial numbers.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Date Text | |||
| 2 | INV-001 | 2024-03-15 | 45366 | ||
| 3 | INV-002 | 2024-04-01 | |||
| 4 | INV-003 | 2024-05-20 | |||
| 5 |
5 Clean and convert imported sales figures
Imported reports often contain numbers with spaces or non-breaking characters. The goal is to convert the text in B2 into a usable number. =VALUE(TRIM(B2)) first removes extra spaces with TRIM, then VALUE converts the cleaned text to 1200. The same pattern works down the column. This is a common fix for data pasted from PDFs or web pages.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales Text | |||
| 2 | North | 1,200 | 1200 | ||
| 3 | South | 950 | |||
| 4 | East | 1,475 | |||
| 5 |
Common VALUE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The text cannot be interpreted as a number, for example "abc" or a date in an unrecognised format. | Check the text for typos or unsupported characters. Use TRIM and CLEAN to remove spaces and non-printing characters, or convert the value with DATEVALUE for dates. |
| #VALUE! with dates | The date text uses a format that Excel does not recognise in your regional settings, such as "15/03/2024" in a US locale. | Use DATEVALUE with a recognised format, or restructure the text with TEXT before converting. |
| #VALUE! with percentages | The percent sign is separated from the number by a space or unusual character. | Remove spaces with SUBSTITUTE or TRIM before applying VALUE. |
| Result looks wrong | The text contains a thousands separator that conflicts with the decimal separator in your regional settings. | Use SUBSTITUTE to standardise separators, or convert with NUMBERVALUE and specify the separators explicitly. |
Tips for using VALUE
- Use
VALUEwhen error checking flags numbers stored as text, or whenSUMreturns zero for a column of visible numbers. - Wrap
VALUEaroundTRIMorCLEANto remove stray spaces and non-printing characters before conversion. - For dates,
VALUEreturns a serial number; apply a date format to the result cell to display it as a date. - For percentages,
VALUE("15%")returns 0.15; format the cell as a percentage to show 15% again. - If you need to control decimal and thousands separators, use
NUMBERVALUEinstead ofVALUE. - Convert once and paste as values if you no longer need the original text, to keep the workbook lightweight.
Frequently asked questions
What is the difference between VALUE and NUMBERVALUE?
VALUE uses your regional settings to interpret separators. NUMBERVALUE lets you specify the decimal and group separators explicitly, which is safer for international data.
Why does VALUE return a number for a date?
Excel stores dates as serial numbers. VALUE converts date text into that serial number. Format the cell as a date to see it normally.
Can VALUE convert text with a currency symbol?
Yes, if the symbol matches your regional currency format. For other symbols or formats, clean the text first with SUBSTITUTE.
Does VALUE work on numbers already stored as numbers?
Yes. If the input is already numeric, VALUE returns the same number unchanged.
How do I convert a whole column of text numbers at once?
Enter =VALUE(A2) in the first cell and fill down. Alternatively, use Text to Columns or multiply by 1, but VALUE is the clearest method.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. VALUE reference