Determining the data type.

S

satadru

Dear All,
Without using the "Format --> Cells --> Number" option, is it possible to
determine the data type / format of a particular cell or a range of cells?

I am trying to look for a formula or a piece of code which could possibly
help me out in this.

Many thanks in advance.
 
B

Bob Phillips

To an extent, yes

=CELL("format",A1)

If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"
 
R

Rick Rothstein

You should be able to construct your own code built around the code concepts
in this macro...

Sub WhatIsIt()
With ActiveCell
If IsDate(.Value) Then
MsgBox "It's a date!"
ElseIf WorksheetFunction.IsNumber(.Value) Then
MsgBox "Excel thinks you have a number of some sort."
Else
MsgBox "It looks like text to me."
End If
End With
End Sub
 
S

satadru

Dear All,

I found a solution which solves a part of my problem. The solution is to use
the TYPE(cell reference) function.

If value is TYPE returns
Number 1
Text 2
Logical value 4
Error value 16
Array 64

My problem starts again when I need to checkout on whether its a date field,
or if it contains a formula.

Many thanks in advance.
 
P

Per Jessen

Hi

Try this function, just notice, that TargetCell has to be a single cell:


Function CellNumberFormat(ByRef TargetCell As Range)
If TargetCell.Cells.Count > 1 Then
CellNumberFormat = "#Range"
Exit Function
End If
f = TargetCell.NumberFormat
CellNumberFormat = f
End Function

Regards,
Per
 
S

satadru

Thanks Per for the solution offered.

Per Jessen said:
Hi

Try this function, just notice, that TargetCell has to be a single cell:


Function CellNumberFormat(ByRef TargetCell As Range)
If TargetCell.Cells.Count > 1 Then
CellNumberFormat = "#Range"
Exit Function
End If
f = TargetCell.NumberFormat
CellNumberFormat = f
End Function

Regards,
Per
Click to expand...
 
Top