Determining The Data Type Of A Cell
In some situations you may need to determine the type of data in a cell. Excel provides a number of built-in functions that can help. These include ISTEXT, ISLOGICAL, and ISERROR. In addition, VBA includes functions such as IsEmpty, IsDate, and IsNumeric.
The CellType function (VBA code is listed below) accepts a range argument and returns a string that describes the data type of the upper left cell in the range. The function returns one of the following strings: Blank, Text, Logical, Error, Date, Time, or Value.
The CellType function
Function CellType(c) ' Returns the cell type of the upper left ' cell in a range Application.Volatile Set c = c.Range("A1") Select Case True Case IsEmpty(c): CellType = "Blank" Case Application.IsText(c): CellType = "Text" Case Application.IsLogical(c): CellType = "Logical" Case Application.IsErr(c): CellType = "Error" Case IsDate(c): CellType = "Date" Case InStr(1, c.Text, ":") <> 0: CellType = "Time" Case IsNumeric(c): CellType = "Value" End Select End Function
Using the CellType function
To use this function in a worskheet, just copy the code and paste it to a module. Then, you can enter a formula such as:
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks