Cell Values And Displayed Text

When you are working in Excel, either with formulas or with Visual Basic For Applications (VBA) code, it is important to understand the difference between the actual value of a cell and what is displayed on the screen. The actual value of the cell is what Excel stores internally and what it uses in formulas and calculations. This is not necessarily the same as what you see displayed on the screen or printed on your reports. It is important that you understand the difference between the two -- otherwise, your formulas may not work as expected.

For example, as you know, dates are stored as the number of days since 0-Jan-1900. (Click here for more information about working with dates and times.) Excel will store the date as a simple number. The date 7-April-2001 is stored internally by Excel as the number 36,988. However, it is quite unlikely that you will display a date in this "serial" format. More likely, you'll display it as 4/7/2001 or perhaps a day name (e.g., "Sat"). On this page, we'll refer to actual value of the cell as "value" and the displayed text as "text".

Cell Values In Formulas

Let's look at this example further. In cell A1, enter 7-April-2001. This should display in the default format for your language. Now, in A2, enter the formula =IF(A1="4/7/2001","Yes","No"). This will display No, because the value of A1 is not the character string "4/7/2001". Now, in A3, enter the formula =IF(A1=36988,"Yes","No"). This will return Yes, because the value of the cell is, in fact, 36,988.

It is unlikely that you'll hard code dates like this. However, you may often format a cell to display just the day of week. Change the number format of A1 to ddd. This will display Sat. In cell A4, enter the formula =IF(A1="Sat","Yes","No"). Again, this will return No, because the value of A1 is not equal to Sat. The text of A1 is indeed Sat, but the value is not.

You can use the TEXT function to format the value of a cell in a formula, and then compare that to another value. For example, you can use the following formula to determine if A1 is a Saturday =IF(TEXT(A1,"ddd")="Sat","Yes","No").

The distinction between value and text is important not just for dates, but for numbers as well. For example, suppose B1 contains the number 0.4999999, but is is formatted to display only one decimal place. In this case, B1 will display 0.5. But the formula =IF(B1>=0.5,"Yes","No") will return No, because the value of B1 is still less than 0.5, even though it appears to be equal to 0.5. In a case like this, you may want to use the ROUND function to round the value properly before the comparison: =IF(ROUND(B1,1)>=0.5,"Yes","No") .

If you fail to take into account the differences between a cell's actual value and the text that is displayed on the screen, your formulas may not work as expected, and it may appear the Excel isn't working properly when in fact it really is.

Cell Values In VBA

When you are writing code in VBA, you can use the Text property of a Range object to get the text which is displayed on the screen. The Value property returns the actual value of the cell. For example,

Range("C1").Value = 0.49999999 Range("C1").NumberFormat = "0.00" Debug.Print "The Value property is: " & Range("C1").Value Debug.Print "The Text property is: " & Range("C1").Text

You'll need to have the Immediate window of the VBA Editor visible in order to see these results. Press CTRL+G or choose Immediate Window from the View menu. The Text property is read-only. You cannot assign a value to this property. It should also be noted that while a cell may contain up to 32K characters of text, the Text property is limited to 1024 characters.