How to Use the VALUE Function in Microsoft Excel

Don’t listen to anyone who tells you that the VALUE function in Microsoft Excel is obsolete. You’ll probably seldom need to use it on its own, but when combined with other functions, it’s still as powerful today as when it was first introduced in 2007.

The VALUE Function: Syntax and Introduction

Excel’s VALUE function has only one argument:

=VALUE(a)

where a is the value stored as text that you want to convert into a number. This can be hard-coded inside double quotes or a reference to a cell containing the value to convert.

In its simplest form, the VALUE function converts numbers stored as text into true numeric values. This is particularly useful if you’ve imported data from another source, and Excel misinterprets numeric values as text. Similarly, numbers can be stored as text if you copy and paste data into Excel, and the program doesn’t recognize values as numbers. In both these cases, you can use the VALUE function to duplicate the data into another cell in the correct numeric format.

The function can also be used to convert other text strings, such as currency strings (such as $10,000), percentages (like 35.7%), and times (such as 14:00), into their numeric equivalents.

However, Excel usually recognizes numbers stored as text and gives you the option to convert them into numeric values without using a function at all. As you can see in the screenshot below, Excel adds a green tag to the cells containing this error.

If you then hover over one of these cells, or select all the affected cells and hover over the selection, you can click the error icon, and choose “Convert To Number” to fix the issue.

At this point, you might be thinking, “What’s the point in the VALUE function if Excel can do the hard work for me?” In fact, the VALUE function remains extremely useful in certain contexts. Specifically, I’ll show you how it comes in handy when using text functions to organize and tidy your figures for analysis.

When numbers are combined with letters in a cell, Excel stores them as text values. For example, the product codes in this Excel table comprise a letter followed by three digits, meaning they’re seen as text.

Your aim is to extract the numbers from each product code as numeric values into column B so you can use them in formulas down the line.

The first step is to use the RIGHT function to extract the numbers from the codes:

=RIGHT([@Product],3)

where [@Product] is the column containing the string from which you want to extract the characters, and 3 tells Excel to extract the three rightmost characters.

However, because the RIGHT function is a text function, the numbers you extracted are stored as text. This means you won’t be able to use them as numeric values in formulas. To fix this, nest the whole formula inside the VALUE function:

=VALUE(RIGHT([@Product],3))

Now, the three-digit values are stored as numbers.

You can use the same principle with the LEFT and MID functions. Here, VALUE and LEFT are used to extract the three numbers at the start of the codes as numeric values:

=VALUE(LEFT([@Product],3))

where [@Product] is the column containing the original string, 3 tells Excel to extract the three leftmost characters, and the VALUE function turns these characters from text values into numeric values.

This time, the middle three characters are extracted using the MID function, before being converted into numeric values using the VALUE function:

=VALUE(MID([@Product],2,3))

where [@Product] is the column containing the string from which you want to extract a certain number of characters, 2 tells Excel to start this extraction at the second character, 3 tells Excel to extract three characters from the starting point, and the VALUE function turns the extracted characters into numeric values.

The VALUE function can be a real time-saver when you’re looking to tidy up disorganized and poorly structured data in your spreadsheet.

Let’s say you’ve been sent this workbook, which contains a string of 1,000 numbers separated (or delimited) by a space—also known as a space-delimited string—in cell A1. Each number in this string is a financial value that you want to extract into individual cells in column A so that you can perform related analyses.

To do this, first, use the TEXTSPLIT function to split each entry in the string into individual rows:

=TEXTSPLIT(A1,," ")

where A1 is the cell containing the text string, the second argument of the TEXTSPLIT function (the column delimiter) is omitted because you want the result in one column, and ” “ tells Excel that the delimiter is a space. If the numbers were separated by commas rather than spaces, you would type a comma inside the double quotes instead of a space.

However, since TEXTSPLIT is a text function, the result is an array of text values, even though they look like numbers. So, you need to wrap the whole formula inside VALUE to convert these values into numeric ones:

=VALUE(TEXTSPLIT(A1,," "))

Now that the values are numeric, you can select all the cells and apply the Accounting number format via the Number group of the Home tab on the ribbon.

To convert the dynamic array into fixed values, select the cells where the result is spilled, press Ctrl+C to copy their content, and press Ctrl+Shift+V to paste them as values.

Finally, now that the result is no longer linked to cell A1, you can delete the original space-delimited string of numbers and format the newly presented data as a table.

Thanks to these quick steps and the power of the VALUE function, you’ve turned a confusing text string into a dataset that is ready to be analyzed.

Extracting Delimited Text-Number Strings Into Separate Columns

In the example above, we used VALUE with TEXTSPLIT to convert all the resultant numbers into numeric values. However, you need to use a slightly different method to split a delimited string of text and numbers while converting the numbers to true numeric values.

Suppose you’ve asked a coworker to use an Excel spreadsheet to stock-check each clothing item, tallying small, medium, and large items separately. However, when he shared the resultant spreadsheet with you, you realize that he used commas to separate each tally, rather than putting them in individual columns.

As a result, in cell B2, you need to restructure the data using the TEXTSPLIT function, and then use the fill handle to copy the formula to the remaining cells:

=TEXTSPLIT(A2,",")

where A2 is the first cell containing the delimited text-number string, and “,“ tells Excel that the column delimiter is a comma.

Although, on first impression, it looks like you’ve solved the problem, the numbers in columns C, D, and E are currently stored as text. You know this because they’re left-aligned.

As a result, if you go to use these numbers in formulas, you won’t get the result you expected.

To fix this, you might wrap the VALUE function around the TEXTSPLIT formula to convert the numbers into numeric values:

=VALUE(TEXTSPLIT(A2,","))

Now, the totals in column F are correct, and the numbers in columns C to E are right-aligned, meaning you’ve successfully turned them into numeric values. However, notice the errors in column B. This happens because the VALUE function has tried (but failed!) to convert the text before the first delimiter into a number.

But don’t fret—there’s one more step you can take to rectify this. Specifically, you need to introduce the IFERROR function, which returns a custom value when a formula generates an error. To demonstrate how this works, wrap the whole formula inside IFERROR, and type x inside double quotes for the custom return value:

=IFERROR(VALUE(TEXTSPLIT(A2,",")),"x")

As you can see in the screenshot above, IFERROR has run into an error in column B—because the VALUE function tried to convert the text values in the string into numbers—returning a series of x values as a result. So, now, you need to tell Excel to return the original text value instead. To do this, where you previously typed x, repeat the TEXTSPLIT formula you already used earlier in the formula:

=IFERROR(VALUE(TEXTSPLIT(A2,",")),TEXTSPLIT(A2,","))

Finally, since the above formula is still linked to the original strings in column A, copy and paste the result as values (Ctrl+C > Ctrl+Shift+V), convert the data into a table, and delete the data in column A.

Now that you understand how the VALUE function works, particularly its ability to work alongside text functions to distinguish numbers and text, go one step further and use it when creating custom data validation rules for cells that require strict combinations of letters and numbers in a particular order.

OS

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.