DisplayedText?

[James Allen]

Hi,

Is there no function (Excel 2002) to return the Text() of another cell, as
it is displayed?
For example, if I had a formula =DisplayedText(Data!A1), I would want it to
return:
"" if Data!A1 is blank
"" if Data!A1=0 and Zeros are not displayed on Data
"0" if Data!A1=0 and Zeros are displayed on Data
"z1" if Data!A1=1 and its format = zGeneral
etc.

I thought =Text(Data!A1,Cell("format",Data!A1)) would do it, but tried and
discovered otherwise. I'm sure a user defined function could be programmed
for this but it sure seems like it would already be built in.

Thank you,

 

[Bernie Deitrick]

James,

It isn't built-in. But the User-Definded-Function, below, will do what you want.

Function DisplayedText(myCell As Range) As String
DisplayedText = myCell.Text
End Function

Use it as in your example:

=DisplayedText(Data!A1)

HTH,
Bernie
MS Excel MVP

 

[James Allen]

That will work. Thank you Bernie.
--
James Allen
Malicoat-Winslow Engineers, P.C.
Columbia, MO

James,

It isn't built-in. But the User-Definded-Function, below, will do what
you want.

Function DisplayedText(myCell As Range) As String
DisplayedText = myCell.Text
End Function

Use it as in your example:

=DisplayedText(Data!A1)

HTH,
Bernie
MS Excel MVP

<snip>