Determining How An Existing Cell Is Formatted ?

[Bob]

ello,

Is it possible to see how a particular is formatted for ?

I did a little experimenting, and it seems that if I put in text, or
numbers, and go to Format Cell, it "seems" to indicate the Format is
General, for both.

Is there some command that would return an inquiry to a text cell as it
truly being "Text" ?

And, I understand that text when entered shows up to the left of the
cell, numbers to the right. If I change the left-right position with
the Alignment buttons, does this change the actual Format of the cell ?

Thanks,
Bob

 

[Gord Dibben]

See help on Information Functions, specifically the CELL function which returns
type of format.

Changing alignment does not change format.

Note that a valid Date is a number so ISNUMBER will return TRUE


Gord Dibben MS Excel MVP

 

[Bob]

Hi,

Thanks for help.

I guess I should have mentioned that I'm reall dumb with this;
new user, truly.

I looked at where you suggested, and found:

info_type Required.

A text value that specifies what type of cell information you want to
return. The following list shows the possible values of the info_type
argument and the corresponding results.
info_type Returns "address" Reference of the first cell in reference,as
text.

O.K. I click in the cell that I'm interested in know if it is formatted
as Text or as a Number.

Now, specifically what do I do next, please ?
What do I type for info_type ?
Where do I type it ?
etc.

Thanks again,
Bob

 

[Gord Dibben]

To find things out about a cell you do not click in that cell.

You select another cell and write a formula referring to the cell in question.

Type your name into A1

In B1 enter =ISTEXT(A1)

Should return TRUE

In C1 enter =ISNUMBER(A1)

Should return FALSE

For info-type you would enter in D1 =CELL("type",A1)

Will return l indicating text as in "label"


Gord

 

[Ron Rosenfeld]

Hi,

Thanks for help.

I guess I should have mentioned that I'm reall dumb with this;
new user, truly.

I looked at where you suggested, and found:

info_type Required.

A text value that specifies what type of cell information you want to
return. The following list shows the possible values of the info_type
argument and the corresponding results.
info_type Returns "address" Reference of the first cell in reference,as
text.

O.K. I click in the cell that I'm interested in know if it is formatted
as Text or as a Number.

Now, specifically what do I do next, please ?
What do I type for info_type ?
Where do I type it ?
etc.

Thanks again,
Bob

You will need VBA to determine that.

You can use a User Defined Function, but be aware that merely changing
the format will not trigger the formula to recalculate. If this might
be a problem, there are a variety of solutions, but they depend on
more information about your worksheet.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual
Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=fmt(A1)

in some cell.

This will return the same kind of string that you see in the Format
Cells dialog.

So if the cell is formatted as text, the formula will return "@"

You could certainly add to the VBA UDF logic to decode all of this;
again, it depends on how you are going to be using this.

==============================
Option Explicit
Function Fmt(cell As Range) As String
Application.Volatile
Fmt = cell.NumberFormat
End Function
====================

 

[Ron Rosenfeld]

For info-type you would enter in D1 =CELL("type",A1)

Will return l indicating text as in "label"


Gord

If I understand the OP correctly, I think he wants to know whether the
cell is formatted as General or Text.

I believe the "type" parameter will return l regardless of the
formatting of the cell, if it contains "label"

 

[Gord Dibben]

You are correect Ron

Excel does not distinguish between Text or General using any of the CELL
arguments

=CELL("format",A1) will return G if A1 contains text

ISTEXT would have to employed.

I posted that alternative.


Gord