# Thread: Hiding Part of a cell's contents

1. ## Hiding Part of a cell's contents

Someone in the office swears he was shown a way -- of course he can't remember now -- of entering a value in a cell that contained a formula without losing the formula, but without the formula being calculated or visible in the cell (although it would be visible in the formula bar). That is, in a cell containing the formula =2*4, he is saying he could edit the cell to put the value 25 in front of the formula followed by a mystery character that would hide the formula in the cell so the cell would read 25, but the formula bar would read 25?=2*4. In addition, he says that the value of 25 in the cell would be a number, not text and thus be could be referenced in other formulas. I've never heard of this, but I'm willing to learn....

Thanks!

2. ## Re: Hiding Part of a cell's contents

There may be another way, but here is the best way I could come up with:

If the cell contains =2*4, then:

1- Put a single quote in front of the Equal sign
2- Press the End key to move the cursor to the end of the formula
3- Hold the Alt key down and press Enter
4- Type what you want to display
5- Make sure that the cell height is only enough to display one line.

3. ## Re: Hiding Part of a cell's contents

Actually, I just figured out a second way that may be a little closer to what you wanted:

1- Select the cell
2- Select Cells from the Format menu and click on the Alignment tab
3- In the Vertical alignment drop down list select "Top" and click OK
4- In the cell, type 25 in front of the equal sign and then hold down Alt and press Enter

Very similarf to my previous post, but a little easier.

4. ## Re: Hiding Part of a cell's contents

Are you and your colleague sure that is not the other way round, i.e the Cell shows 25 =2*4 whilst the formula bar just shows 25. (That can be achieved by formatting the number as # "=2*4". If you enter 25 =2*4 into a cell it cannot evaluate to 25 except by using something like =LEFT(x,2) where x is the cell containing the expression.

Maybe there is a character that tells Excel to disregard anything that follows it, but what exactly would be the point?. Unless it could be a way of inserting a comment, before the introduction of comments as we now know them. Perhaps a remnant from Lotus 123 for DOS!. If it can be done then somebody must know.

Andrew C

5. ## Re: Hiding Part of a cell's contents

My coworker insists he simply typed some kind of character as a separator between the the number and the formula and that formula would be hidden in the cell and the number would still evaluated as a number. Although Legare's suggestion can be used to display the number while hiding the formula, the number is not evaluated as a number.

6. ## Re: Hiding Part of a cell's contents

Well we should find this hidden character, it would be a nice way of putting a reminder into a cell. I am sure that if it does exist somebody will let us know.

AC

7. ## Re: Hiding Part of a cell's contents

Are you sure your collegue was talking about Excel and not Quattro Pro? In QP you can enter a semicolon after a value in a cell then enter something else and only the value will be visible in the cell but everything will be visible in the formula bar.

8. ## Re: Hiding Part of a cell's contents

No, it was Excel. We've never used Quattro Pro.

9. ## Re: Hiding Part of a cell's contents

It seems if you enter:

=2 + n("1+1")
in a cell all that will appear in the cell will be the 2, everything will be visible in the formula bar and the cell will still be accepted as a number, capable of being used in formulas. It's pretty neat.

10. ## Re: Hiding Part of a cell's contents

N is a function which returns a value for numerical data and 0 for text data. So therefore N("1+1") = 0, which makes 2 + N("1+1") = 2 (i.e. 2 + 0). If you rfemove the parentheses from "1+1", the expression returns a value of 4. So while it may look interesting it is effectively the same as entering = 2 + 0, hence it does not really qualify.

Andrew C

11. ## Re: Hiding Part of a cell's contents

Actually this does accomplish what my coworker was looking for, being able to preserve an existing formula in a cell without it being calculated while at the same time plugging in a different value that could be used in other calculations that referenced the cell. By using the N function you can retain the text of the original formula for posterity, and play some what-if scenarios with other values. Good job!

12. ## Re: Hiding Part of a cell's contents

I understood that a mystery character was involved that allowed the following to exist in a cell and return a numerical value : - 25? = 2*4 where ? is a mystery character - not quiet the same as the N() function, which incidentally is calculated in the above formula, it just happens to evaluate to 0. Change the + sign to * and you will see what I mean. =25 + 0*(2*4) achieves much the same thing. Pity there is no mystery character [img]/w3timages/icons/sad.gif[/img]

AC

13. ## Re: Hiding Part of a cell's contents

Sorry about the mystery character turning out to be a dry hole, but I was going on someone's else vague memory.

14. ## Re: Dry Hole

No problem Tim, there is always something to be learnt even from a dry hole as you put it. It was worth the excursion - at least the N() function has a use after all (though the same effect could be achieved without it). Now can you come up with a good use for the T() function ? [img]/w3timages/icons/dizzy.gif[/img]

regards,

Andrew C

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•