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.

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"


--
__________________________________
HTH

Bob

"satadru" wrote in message
...
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.

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

--
Rick (MVP - Excel)


"satadru" wrote in message
...
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.

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.
"satadru" wrote:

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.

Thanks to Bob and Rick. Your solutions are the ones which helped solve my
problem.

"satadru" wrote:

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.
"satadru" wrote:

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.

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

"satadru" skrev i meddelelsen
...
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.

Thanks Per for the solution offered.

"Per Jessen" wrote:

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

"satadru" skrev i meddelelsen
...
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.