How to check or determine data type of cell?
In certain case, you may want to know the data type of the values in the cells, such as blank, error, date or something else. May be your first thought is the Type function in Excel, but this function just can check four types of the data which is not incomplete, so here, I can talk about another method to help you solve this task.
Check or determine the data type with User Defined Function
Check or determine the data type with User Defined Function
In Excel, you can create User Defined Function to check the data type, please do with the following steps:
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
VBA code: Check or determine the data type in Excel
Function CellType(pRange As Range)
'Updateby20140625
Application.Volatile
Set pRange = pRange.Range("A1")
Select Case True
Case VBA.IsEmpty(pRange): CellType = "Blank"
Case Application.IsText(pRange): CellType = "Text"
Case Application.IsLogical(pRange): CellType = "Logical"
Case Application.IsErr(pRange): CellType = "Error"
Case VBA.IsDate(pRange): CellType = "Date"
Case VBA.InStr(1, pRange.Text, ":") <> 0: CellType = "Time"
Case VBA.IsNumeric(pRange): CellType = "Value"
End Select
End Function
3. Then save and close this code, go back to the worksheet, and enter this formula =CellType (A1) into a blank cell besides your data range, see screenshot:
4. Then drag the fill handle over to the cells which you want to contain this formula, and all data types have been extracted from the cells, see screenshot:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
