How To: Use Cell("Format") to return MMMM YYYY instead of D3

[Guest]

Hi
I'm trying to get the format property of a of a cell with using macros just using worksheet functions. When I use Cell("Format",A1) it returns D3, but I need the MMMM YYYY custom format that I have placed on the cell to be returned, any ideas how to get it

TI
KM

 

[Frank Kabel]

Hi
AFAIK this is not possible with using only worksheet
functions

-----Original Message-----
Hi,
I'm trying to get the format property of a of a cell

with using macros just using worksheet functions. When I
use Cell("Format",A1) it returns D3, but I need the MMMM
YYYY custom format that I have placed on the cell to be
returned, any ideas how to get it?

 

[Guest]

What does AFAIK mea

----- Frank Kabel wrote: ----

H
AFAIK this is not possible with using only worksheet
function

-----Original Message----
Hi
I'm trying to get the format property of a of a cell

with using macros just using worksheet functions. When I
use Cell("Format",A1) it returns D3, but I need the MMMM
YYYY custom format that I have placed on the cell to be
returned, any ideas how to get it

 

[Thomas Ramel]

Grüezi Kevin

Kevin McCartney schrieb am 18.05.2004

I'm trying to get the format property of a of a cell with using macros
just using worksheet functions. When I use Cell("Format",A1) it returns
D3, but I need the MMMM YYYY custom format that I have placed on the
cell to be returned, any ideas how to get it?

There might be a possibility when you use xl4Macros in a defined name.

Insert --> Names --> Define --> Names in Workbook: 'Cellformat' --> Refers
to:

=GET.CELL(7;INDIRECT("RC(-1)";FALSE))

--> [Add] --> [OK]

When you like to see the format of A1, then:

B1 =Cellformat

'Cellformat' evaluates the cell left of where you use it in the table.

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

 

[Frank Kabel]

Hi
AFAIK: As far as I know

-----Original Message-----
What does AFAIK mean

----- Frank Kabel wrote: -----

Hi
AFAIK this is not possible with using only worksheet
functions
cell
with using macros just using worksheet functions. When I
use Cell("Format",A1) it returns D3, but I need the MMMM
YYYY custom format that I have placed on the cell to be
returned, any ideas how to get it?
.

 

[Tom Ogilvy]

it might be important to note that if you copy a cell using this formula and
paste it on another sheet, in xl2000 and earlier, this will cause a general
protection fault, excel will close and you will loose any changes to your
workbook.

I believe xl2002 and possibly xl2003 are more tolerant.
--
Regards,
Tom Ogilvy

Grüezi Kevin

Kevin McCartney schrieb am 18.05.2004

I'm trying to get the format property of a of a cell with using macros
just using worksheet functions. When I use Cell("Format",A1) it returns
D3, but I need the MMMM YYYY custom format that I have placed on the
cell to be returned, any ideas how to get it?

There might be a possibility when you use xl4Macros in a defined name.

Insert --> Names --> Define --> Names in Workbook: 'Cellformat' --> Refers
to:

=GET.CELL(7;INDIRECT("RC(-1)";FALSE))

--> [Add] --> [OK]

When you like to see the format of A1, then:

B1 =Cellformat

'Cellformat' evaluates the cell left of where you use it in the table.

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

 

[Thomas Ramel]

Grüezi Tom

Tom Ogilvy schrieb am 18.05.2004

it might be important to note that if you copy a cell using this formula and
paste it on another sheet, in xl2000 and earlier, this will cause a general
protection fault, excel will close and you will loose any changes to your
workbook.

Thanks for upgrading my knowledge

I believe xl2002 and possibly xl2003 are more tolerant.

It may be that SP3 fixed this problem - Excel refuses to paste this
'macro-formula', but it stays alive.

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]

 

[David McRitchie]

Hi Kevin,
For dates and time you may not necessarily get what you ask for
because Excel may take your format and say that is Kevn's regional
format. But see if this gets what you want -- it definitely will not
give you D3.
See my http://www.mvps.org/dmcritchie/excel/formula.htm#getformat

Function GetFormat(Cell as Range) as String
GetFormat = cell.NumberFormat
End Function

=GetFormat(A3)
=Personal.xls!GetFormat(A3)-

Hi,
I'm trying to get the format property of a of a cell with using macros just using worksheet functions. When I use

Cell("Format",A1) it returns D3, but I need the MMMM YYYY custom format that I have placed on the cell to be returned, any ideas how
to get it?

 

[Harlan Grove]

For dates and time you may not necessarily get what you ask for
because Excel may take your format and say that is Kevn's regional
format. But see if this gets what you want -- it definitely will not
give you D3.
See my http://www.mvps.org/dmcritchie/excel/formula.htm#getformat

Function GetFormat(Cell as Range) as String
GetFormat = cell.NumberFormat
End Function ...

In other words, there's no way to do this without using some form of macro
programming, either XLM formulas in defined names (which can be dangerous in
older XL versions) or VBA.

If the OP can't use VBA or XLM, the OP can't get the number format.