DisplayedText?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Allen - 19 Mar 2007 16:54 GMT

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,

Signature

James Allen
Malicoat-Winslow Engineers, P.C.
Columbia, MO

Reply to this Message

Bernie Deitrick - 19 Mar 2007 19:09 GMT

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

> Hi,
>
[quoted text clipped - 11 lines]
>
> Thank you,

Reply to this Message

James Allen - 20 Mar 2007 14:55 GMT

That will work. Thank you Bernie.

Signature

James Allen
Malicoat-Winslow Engineers, P.C.
Columbia, MO

> James,
>
[quoted text clipped - 12 lines]
> Bernie
> MS Excel MVP

<snip>

Reply to this Message