How to use the value of a cell in its displayed format?

[Stefano Gatto]

Hi,

I would like to know if there is a way to refer in a formula to the
displayed sequence of characters of a value contained in a cell.

For more clarity let me show an example:
- In cell C5 I have number 12.
- I set the format of C5 to "0.00".
- Cell C5 displays sequence "12.00" on the screen
- I need to display in C8 the sequence of chars displayed in C5 followed by
" is the maximum"
- If I type in C8 =C5&" is the maximum", this will return "12 is the
maximum", whereas I want "12.00 is the maximum".

Question: is there a worksheet function that returns the displayed string of
characters (which depends on the format setting for that cell)?

Formula =TEXT(C5,"0.00")&" is the maximum" is not really an answer to my
question because I need to know in advance what is the format of C5 and
repeat it explicitly in C8. =CELL() also does not return ANY format that may
be set in C5, it only returns some few predefined formats.

Is there something else?

Thank you for any help you might provide me.

 

[Andy Brown]

Formula =TEXT(C5,"0.00")&" is the maximum" is not really an answer ...

=VALUE(C5)&" is the maximum"

perhaps?

HTH,
Andy

 

[Stefano Gatto]

No, this does exactly the opposite of what I need. I need a function that
given the address of a cell returns a string of what is *displayed* in that
cell (and not *contained* which is what VALUE does).

This is another example:

If I have 68 in cell D4 and format of D4 is namely "A" then the displayed
chain of characters in D4 will be "A". Ok so far? Well what I need is a
function (call it F(address)) that when given the address D4 (or $D$4)
returns "A".

So if cell H11 contains =F(D4) then H11 displays "A".

I hope this is clearer that what I explained before.

Thank you and have a nice day.

Stefano Gatto

 

[Andy Brown]

I hope this is clearer that what I explained before.

Sure. If the format codes aren't enough, then ???

There may be a way to do it with code, I wouldn't know.

The only thing I can think is to link C5 to *another* file where precision
as displayed is set (Tools -- Options -- Calculation), then link back to C8.

HTH,
Andy

 

[Stefano Gatto]

OK, so I understand I am not disregarding an Excel function that exists to
return that...

Using "precision as displayed" would only help for some numerical formats.
It wouldn't help for other formats like say "AAA" or "d-mmmm-yy dddd" for
example.

Thanks for your patience Andy and have a good day.

 

[Andy Brown]

OK, so I understand I am not disregarding an Excel function that exists to

return that...

Not that I'm aware of.

Regrets,
Andy