You have numbers in Excel that won’t add up or sort correctly. They are stored as text, often because they were imported from another system. This prevents you from using them in calculations. The VALUE function changes this text into a real number that Excel can calculate with. This article shows you how to use the VALUE function to fix this problem.
Key Takeaways: Converting Text to Numbers with VALUE
- VALUE function: Converts a text string that represents a number into a numeric value that Excel can use in formulas.
- Paste Special > Multiply: A quick method to convert an entire column of text-numbers by multiplying them by 1.
- Error Checking green triangle: Identifies cells with numbers stored as text and offers a one-click conversion option.
What the VALUE Function Does
The VALUE function takes a text string and converts it into a number. It is designed for text that clearly looks like a number, date, or time. For example, it can convert the text “123.45” into the numeric value 123.45. It can also interpret dates and times formatted as text, like “1/15/2024”, into their underlying serial numbers.
Numbers stored as text are a common data import issue. They appear right-aligned like numbers but have a small green triangle in the top-left corner of the cell. You cannot sum a column of these values with the SUM function. The VALUE function solves this by providing a clean, formula-based conversion. You need a cell containing the text you want to convert before you can use the function.
Steps to Use the VALUE Function
- Select the result cell
Click on an empty cell where you want the converted number to appear. This is where you will enter the formula. - Type the VALUE formula
Type an equals sign, the word VALUE, and an opening parenthesis. The formula should look like this: =VALUE( - Reference the text cell
Click on the cell that contains the text you want to convert, or type its address like A1. Your formula will now look like =VALUE(A1). - Complete the formula
Type a closing parenthesis and press Enter. The cell will now display the numeric value. The original text in cell A1 remains unchanged. - Apply to multiple cells
To convert a whole column, click the cell with your first VALUE formula. Drag the fill handle (the small square at the cell’s bottom-right corner) down the column to copy the formula. - Replace formulas with values
Select the column of results you just created. Press Ctrl+C to copy. Right-click on the same selected area, choose Paste Special, and then select Values. This removes the formula and leaves only the static numbers.
Using Paste Special to Multiply
If you have a column of text-numbers and do not need a formula, use Paste Special.
- Enter the number 1
Type the number 1 into any empty cell and copy it by pressing Ctrl+C. - Select your text data
Highlight the entire range of cells containing the text that looks like numbers. - Open Paste Special
Right-click on the selected range and choose Paste Special from the menu. - Choose Multiply
In the Paste Special dialog box, under the Operation section, select Multiply. Click OK. This forces Excel to re-evaluate all selected cells as numbers.
Common Mistakes and Limitations
VALUE Function Returns a #VALUE! Error
This error means the text in the referenced cell cannot be recognized as a number. Check for hidden spaces, currency symbols not supported by your locale, or letters mixed with digits. Use the TRIM function to remove spaces: =VALUE(TRIM(A1)).
Numbers Still Not Calculating After Conversion
If your numbers still do not sum, the cells may be formatted as Text. Select the converted numbers, go to the Home tab, and in the Number group, choose General or Number from the dropdown. Then re-enter the data by pressing F2 and Enter on one cell, or use Paste Special > Values again.
Leading Zeros Disappear
The VALUE function and numeric formats will drop leading zeros, as they are not mathematically significant. To preserve a format like “00123”, you must apply a custom number format after conversion. Select the cells, press Ctrl+1, choose Custom, and type 00000 in the Type field.
VALUE Function vs. Other Conversion Methods
| Item | VALUE Function | Paste Special Multiply | Error Checking Tool |
|---|---|---|---|
| Primary Use | Formula-based conversion within calculations | Bulk, one-time conversion of static data | Quick fix for individual cells marked with an error indicator |
| Result Type | Dynamic formula that updates if source text changes | Static numeric value | Static numeric value |
| Best For | Building datasets where source text may update | Cleaning imported data before analysis | Correcting a few problematic cells quickly |
| Handles Non-Numeric Characters | Fails with #VALUE! error | Fails and leaves cells unchanged | Does not typically flag these cells |
You can now convert text that looks like numbers into actual values using the VALUE function. This allows you to sum, sort, and use the data in all Excel calculations. For a faster one-time cleanup, remember the Paste Special > Multiply trick. A concrete advanced tip is to combine VALUE with TRIM and CLEAN in one formula, like =VALUE(TRIM(CLEAN(A1))), to remove spaces and non-printing characters before conversion.