Show/Hide Cell Content (Partial)

[bkessinger]

Good Evening All:

Is it possible to show only partial content of a cell, e.g., Input 10 character but only show 6. TIA.

Bill Kessinger

[HansV]

Excel is not really good at that. Here are two different options:

1) Edit the cell in the formula bar.
Select the characters that you don't want to display.
Select the same color for the text as you're using for the background (usually white).
This will only hide the characters in the cell itself. They will still be visible in the formula bar.
If you select a range of cells including the one with the partially hidden text, the hidden characters will be faintly visible.
They may also be printed - make sure to test that.

2) Put a rectangular shape over the part of the cell contents that you want to hide.
Unless you protect the sheet, users will be able to move or delete the rectangle.

[bkessinger]

Thanks Hans. I'll give it a try.

Bill K.

[teachesms]

Hans,

In my spare time I read the board to learn as much as I can...I love this place! :) I just noticed while playing around with this question in Excel 2010 that:

1. If you precede the number in the cell with the single quote for text then format the first few numbers as white font, when highlighted they don't show...only in the formula bar, but not the cell.

2. If you don't preced the number in the cell with the single quote for text but rather use the Format Cells dialog to make the cell a text cell it does show when highlighted as you mentioned.

3. If its a number in the cell, you can forget it. It's not going to allow any font color at all.

If this is true, then one could protect the cells that contain the text which begins with a ' (single quote), and in the protection tell it to hide the formula. (though there is none really...it still hides the formula bar content.)

Just thought I'd tell you what I came up with after messing with it a bit, in case anyone ever needs to know.

[HansV]

When I try it, both a cell whose value is prececed by an apostrophe and a cell whose number format has been set to Text don't show the white text when the cell is selected or when the cell is outside the selection. But if the cell is within the selection but not the active cell, the cell bacground is light gray, thus "exposing" the white text.

In the screenshot below, the value of A1 has an apostrophe before it, A2 is formatted as Text and A3 is the active cell.

x452.jpg

[teachesms]

Rats...Your right!

Sigh

[steveh]

Good Evening All:

Is it possible to show only partial content of a cell, e.g., Input 10 character but only show 6. TIA.

Bill Kessinger

Hi Bill

This is probably way of track but how about for example formatting for example A1:A10 as a white font into which you will type the 10 numbers or whatever and then in B1 put =RIGHT(A1,6) and dragging it down to B10. If you wanted to print it without showing you cuold set the print area to not print column A.

HTH

[StuartR]

How are you collecting the data that you store in this cell?

Could you store the full number in a sheet that has it's visible attribute set to be xlSheetVeryHidden and then use a formula on the visible sheet to just display the characters you want to show?

[bkessinger]

Thanks Steve & Stuart, I'll run these by my client.

Bill K.