Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Determining a Cell's Data Type

Excel provides a number of built-in functions that can help determine the type of data contained in a cell. These include ISTEXT, ISLOGICAL, and ISERROR. In addition, VBA includes functions such as ISEMPTY, ISDATE, and ISNUMERIC.

The following function accepts a range argument and returns a string (Blank, Text, Logical, Error, Date, Time, or Value) that describes the data type of the upper-left cell in the range:

Function CELLTYPE(cell As Range) As String ' Returns the cell type of the upper-left ' cell in a range Dim UpperLeft As Range Application.Volatile True Set UpperLeft = cell.Range("A1") Select Case True Case UpperLeft.NumberFormat = "@" CELLTYPE = "Text" Case IsEmpty(UpperLeft.Value) CELLTYPE = "Blank" Case WorksheetFunction.IsText(UpperLeft.Value) CELLTYPE = "Text" Case WorksheetFunction.IsLogical(UpperLeft.Value) CELLTYPE = "Logical" Case WorksheetFunction.IsErr(UpperLeft.Value) CELLTYPE = "Error" Case IsDate(UpperLeft.Value) CELLTYPE = "Date" Case InStr(1, UpperLeft.Text, ":") <> 0 CELLTYPE = "Time" Case IsNumeric(UpperLeft.Value) CELLTYPE = "Value" End Select End Function

Figure 25-1 shows the CELLTYPE function in use. Column B contains formulas that use the CELLTYPE function with an argument from column A. For example, cell B1 contains the following formula:

=CELLTYPE(A1)

Figure 25-1: The CELLTYPE function returns a string that describes the contents of a cell.