Solved: Formula to capture cell's displayed value

[GreenTree]

Depending on the number format, a cell with the value 4.25 could look like $4.25 or 4 or 4.2500 or 425% or 4 Jan 6:00 AM or etc etc etc.

Let's say that I want to display the same thing that A1 has in it, in B1, but I don't know ahead of time what the number format of A1 is going to be. (There's actually a Lookup involved, and depending on which column is being pointed to, the data may be in any of several formats, but the lookup part isn't the question here.) What should go in B1 so that what the user sees there looks the same (425% -- 425%, or 4.2500 -- 4.2500, or etc) as A1?

(Tried conditional formatting, but that's colors & fonts & backgrounds, not number format. At least not in 2003, which is what I'm using.)

Many thanks,

G.T.

[Bob Phillips]

Try this UDF



Function ShowAsIs(cell As Range)
ShowAsIs = cell.Text
End Function

[GreenTree]

Is there a way to do it without VBA? The spreadsheet that I'm working on is going out to a bunch of people, some of whom use programs besides Excel, and I'd like to make this compatible for them all.

Sorry to be nit-noid about that, and I should have mentioned that in the original post. My bad!

Thanks,

G.T.

[Jan Karel Pieterse]

Yes, it can be done without VBA, but it involves an XLM macro function inside a range name and that will still make Excel prompt the user to enable macros.
See:
www.jkp-ads.com/articles/excelnames08.asp (http://www.jkp-ads.com/articles/excelnames08.asp)

[tpoynton]

There is a way to return the format of a cell (A1 here) using =CELL("format", A1) . However, it does not return the format in a way you can use in a TEXT formula. If there are only a handful of possible number formats, you can do it using a combination of these like below, with if statements to put the number format returned from CELL in a way it can be used in TEXT.

=TEXT(A1,IF(CELL("format",A1)="F2","0.00",IF(CELL("format",A1)="P2","0.00%",IF(CELL("format",A1)="C2","$#,##0.00_);($#,##0.00)"))))

As with all of my posts, I suspect there is a better way to do this and I look forward to learning it as well.

Attached is the sample I toyed with.

[tpoynton]

sorry, hit quote instead of edit

[GreenTree]

Many thanks tpoynton, that will work for what I need just great!

Many thanks also for the other answers -- one more reason why I love this forum!

G.T.

:thumb