Cells display function's text rather than its results

[tonerowlabs]

noob here...

Just created a simple CONCATENATE function combining two text strings on two different worksheets (within same .xls document). The result is a URL. No empty gaps, all formatted as General.

Everything worked just fine the first several times I opened, saved and reopened the document.

Then it started getting weird: dates are suddenly displayed as 5-digit numbers (tried formatting cells as dates, won't change), and the formula is displayed rather than the result. I've tried reformatting, re-launching, etc... and I just can't get it back to the way it was. It's like a huge, irreversible reformatting took place of its own accord. Any tips?

Many thanks in advance.

TRL

(office 2000, win XP)

[tonerowlabs]

function =
=CONCATENATE(Sheet2!A1,A2)

text displayed used to be:
[the correct text string, combining text from 2 fields)

it is now:
"=CONCATENATE(Sheet2!A1,A2)"

date =
07/06/2006

date displayed =
38904

thanks....

[Dave Peterson]

Make sure that the cell is formatted as General and then reenter the formula.

And try something like:

=concatenate(sheet2!a1,text(a2,"mm/dd/yyyy"))
or simply
=sheet2!a1 & text(a2,"mm/dd/yyyy")



tonerowlabs wrote:
>
> function =
> =CONCATENATE(Sheet2!A1,A2)
>
> text displayed used to be:
> [the correct text string, combining text from 2 fields)
>
> it is now:
> "=CONCATENATE(Sheet2!A1,A2)"
>
> date =
> 07/06/2006
>
> date displayed =
> 38904
>
> thanks....
>
> --
> tonerowlabs
> ------------------------------------------------------------------------
> tonerowlabs's Profile: http://www.excelforum.com/member.php...o&userid=36138
> View this thread: http://www.excelforum.com/showthread...hreadid=559127

--

Dave Peterson

[tonerowlabs]

thanks, that definitely worked for the dates.

But there are two separate issues:

1. Dates inputed AS TEXT are appearing as 5-digit numbers.
2. Functions displayed rather than their results.

I mention them together, because I think they are related. They both occurred at the same time (I have no idea what I did), and they both involved text being displayed as formulae. All cells are formatted as General.

Let me ask another way: is there a way to have the CONCATENATE results displayed as the function formula? Lets say you want to look at an entire worksheet and see only the functions/formulae in each cell, rather than the results of them. Is this possible? If so, maybe I accidentally did this, and I want to make it go back?

BTW, the results of the concatenate forumla will simply be a URL. The function grabs the first 20 characters, and then adds the PRODUCT_CODE from another cell, which takes you to a URL for that specific product online. No special characters, all alphanumeric.

THANKS in advance!!!!

[Dave Peterson]

I'm not sure what entering dates as text means, but...

Maybe you're looking at formulas.

Tools|options|view tab|uncheck formulas.

There's a shortcut key that toggles this setting that may have been hit in
error.

Ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard)

And this can be a quick way to look at the formulas, too.

If you want that URL to be a hyperlink, you may want to do something like:

=hyperlink(yourformulahere,"Click Me!")



tonerowlabs wrote:
>
> thanks, that definitely worked for the dates.
>
> But there are two separate issues:
>
> 1. Dates inputed AS TEXT are appearing as 5-digit numbers.
> 2. Functions displayed rather than their results.
>
> I mention them together, because I think they are related. They both
> occurred at the same time (I have no idea what I did), and they both
> involved text being displayed as formulae. All cells are formatted as
> General.
>
> Let me ask another way: is there a way to have the CONCATENATE results
> displayed as the function formula? Lets say you want to look at an
> entire worksheet and see only the functions/formulae in each cell,
> rather than the results of them. Is this possible? If so, maybe I
> accidentally did this, and I want to make it go back?
>
> BTW, the results of the concatenate forumla will simply be a URL. The
> function grabs the first 20 characters, and then adds the PRODUCT_CODE
> from another cell, which takes you to a URL for that specific product
> online. No special characters, all alphanumeric.
>
> THANKS in advance!!!!
>
> --
> tonerowlabs
> ------------------------------------------------------------------------
> tonerowlabs's Profile: http://www.excelforum.com/member.php...o&userid=36138
> View this thread: http://www.excelforum.com/showthread...hreadid=559127

--

Dave Peterson