Excel : cells showing the formula instead of its result? 1

[fanch72]

Hi

In my Excell sheet, all the formulas I create do not produce a result. Instead the cell contains the text of the formula. Has someone an idea why?

Thanks
Francois

 

[MeGustaXL]

Tools, Options, View, Uncheck "Formulas" (bottom left middle)

HTH

 

[fanch72]

THanks, but it still doesn't work...

 

[MeGustaXL]

You're putting an equals sign ( = ) first, aren't you?



Chris

Who cares! Just stop hitting my head!!! - Charlie

 

[xlbo]

Are the cells formatted as text BEFORE you enter the formulae ?

Once you have entered a formula, what happens if you go back in to the formula (F2 or click in the formula bar) and then press return ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fanch72]

The equal signs comes automatically when I insert the formula. Then when I select the cells I want to concatenate, the result is shown properly in the formula box. But when I click OK, I just see the formula. Weird... And F2 and enter don't give any better luck...

 

[GlennUK]

Are you sure that you tried MeGustaXL's hint correctly?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Hi,

Please copy 'n' paste the contents of the FORMULA BAR of a typical formula.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

... and how is that cell FORMATTED in Format/Cells - Number Tab

Skip,
[sub]
[red][/red]
[/sub]

 

[fanch72]

=CONCATENATE(H2495;I2495), but it is the same with other formulas
the format for the cells is "general", I have tried other formating without any success.
and yes I have unchecked the formula....

 

[MeGustaXL]

=CONCATENATE(H2495[red];[/red]I2495)

This should be a colon -

=CONCATENATE(H2495[red]:[/red]I2495)

Chris

Who cares! Just stop hitting my head!!! - Charlie

 

[fanch72]

doesn't help... it's a semi-colon on mine. The formula is just not recognized as a formula but as plain text

 

[MeGustaXL]

Hmmm... If I use a semicolon, Excel finds the error and offers to correct it to a colon.

What version of XL are you using?

Chris

Who cares! Just stop hitting my head!!! - Charlie

 

[bluedragon2]

Is there a ' in front of the = in the formula bar? Not the cell itself. If so delete it.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

Is the format for the cells general before you put the formula in ? or is that what you are changing it to afterwards ?

You say the = sign "comes automatically" when you insert the formula....how exactly are you creating the formula? directly in the cell or by pressing the fx button and going through the formula wizard ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fanch72]

Chris: I have Office 2003. I don't choose whether it is a ; or a : XL does it for me when I select the formula in INSERT FUNCTION.
Bluedragon: well, same answer as for Chris I guess... With or without = it doesn't work.
Is there a place where I can upload the file if you want to check it?

 

[xlbo]

Unfortunately, you cannot upload files to this site. If you have a website yourself, you can load to there and link to it. Other than that, (and this is kinda against site rules), post your emaail address with some filler stuff so that the spambots can't pick it up. I will email you and you can send me the file to look at.

eg filler stuff

$£^%"£$^%YourNameNoSpamNoSpamAt*^*$No%%^&SpamCompanyNameNoSpam$£%£$"%NoSpamDOTNo SpamCom

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Was the data imported from another program? I ask because this is a common problem with imported data. The cause is that the data is formatted as text. Even though you might have tried to change the formatting, even though you can check the formatting of the cells and Excel says they are formatted as general, they are really still formatted as text.

Try this:

[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble[sup][red]*[/red][/sup][/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

[sup][red]*[/red][/sup] Note that any null cells that you select will be converted to zeros. For this reason I recommend that you don’t select an entire row/column.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[kvdwalt]

I had the same problem...i found that if i save the document as a text file and import it agian that it solves this problem...there is something wrong with the file format...document corupted?

 

[searlerm]

You may have alreday solved this but I had the same problem with imported data. I deleted the offending column & inserted a newone; format it as "general" & then re-created the formulae - worked fine.