How to offset cell value from thick border lines?

[nomail1983]

What is the best way to offset cell values (text and numbers) from the
cell border lines?

The problem is: when I select the thickest border line, left- and
right-adjusted cell values (text and numbers) are too close to the left
and right borders. It detracts from readability, especially when the
printed worksheet is faxed.

I have found ways to avoid most of the problem. First, I adjust the
row height (i.e, over-size it) and select Center vertical alignment.
That offsets from the top and bottom borders. Second, for
left-adjusted text, I put a space in the first character position.
That is for cells that must appear fully left-adjusted; other cells are
indented from the left. Third, for right-adjusted numbers, I select a
Custom number format of the form #,##0 " ". Finally, for single-lined
right-adjusted text, I put a space in the last character position.
Those solutions offset from the left and right borders.

However, I have no solution for multi-lined right-adjusted text -- text
that contains an explicit line break (char(10) or alt-Enter). My
solution for single-lined right-adjusted text -- putting a space at the
end of each line -- does not work. I have tried various versions of
that solution to no avail, including "line1 "&char(10)&"line2 ".

(I am using the Arial font, which is proportional. That is the font I
want. But I did try Courier, a non-proportional font -- also to no
avail.)

So I believe my initial question boils down to: for multi-lined
right-adjusted text with explicit line breaks, how can I offset the
text from the right cell border (i.e, move it to the left) by the same
one or two spaces that numbers and single-lined text in the same column
are offset by?

Alternatively, is there a better way to offset left- and right-adjusted
cell values (text and numbers) by the same amount from the left and
right cell borders so that all values (text and numbers) in a column
are aligned on the left or right, depending on the cell alignment?

 

[Dallman Ross]

What is the best way to offset cell values (text and numbers) from the
cell border lines?

Have you tried Format Cells -> Alignment -> Indent ?

I have found ways to avoid most of the problem. First, I adjust the
row height (i.e, over-size it) and select Center vertical alignment.
That offsets from the top and bottom borders. Second, for
left-adjusted text, I put a space in the first character position.
That is for cells that must appear fully left-adjusted; other cells are
indented from the left. Third, for right-adjusted numbers, I select a
Custom number format of the form #,##0 " ". Finally, for single-lined
right-adjusted text, I put a space in the last character position.
Those solutions offset from the left and right borders.

Try custom: #,##0_)

The "_" there causes space to be left. You can use more than one.

-dman-

==================================================

 

[nomail1983]

(e-mail address removed) spake thusly:

Have you tried Format Cells -> Alignment -> Indent ?

Thanks for your response.

Yes I had. But after seeing your response, I tried again, and I
discovered that I had misinterpreted the use of the Indent buttons on
the toolbar with respect to the right margin. It had not occurred to
me that I press "->|" to move right(!).

Okay, right-indent would align everything nicely on the right. But it
offsets things more than I would prefer. I would prefer to move things
to the right "just a skosh", similar to the custom format that I use
(or the one you suggest).

Is there any way to change the amount of movement that Indent causes?
I did not see anything under Tools > Options, but I might have missed
it.

Third, for right-adjusted numbers, I select a
Custom number format of the form #,##0 " ".

[....]
Try custom: #,##0_)

How is that different than or why is that preferred over what I did --
which I should have written as #,##0" " (no extra space)?

In any case, I believe that works only for numeric values, not for
text. The right-index solution would work for both.

BTW, is there any Help text that explains the Custom format syntax? I
did not find anything myself when I searched for "custom format".
However, I did not look at Online Content. (I usually disable that
feature.)

Thanks again.

 

[Dallman Ross]

Dallman Ross wrote:

Thanks for your response.

Yes I had. But after seeing your response, I tried again, and I
discovered that I had misinterpreted the use of the Indent
buttons on the toolbar with respect to the right margin. It had
not occurred to me that I press "->|" to move right(!).

I often hit the wrong icon myself when I happen to want to use
those from the toolbar.

Okay, right-indent would align everything nicely on the right.
But it offsets things more than I would prefer. I would prefer
to move things to the right "just a skosh", similar to the custom
format that I use (or the one you suggest).

Don't know of a way to control that, sorry.

How is that different than or why is that preferred over what I
did -- which I should have written as #,##0" " (no extra space)?

For one thing, the "_" spacer seems narrower to me. So you have
more control. Also, I believe it's just the standard way to do
that. Not to say you couldn't do it the way you discovered.

I even have a custom format for a stock price that is

_("@"* #,##0.00_);_("@"* (#,##0.00);_($* "-"??_);_(@_)

and that produces, for example,

@ 17.03

in the cell.

In any case, I believe that works only for numeric values, not for
text. The right-index solution would work for both.

No, you can format general or other format defaults to custom and
use _ to change them. The four separate formattable statements are
for positive, negative, other (zero), and non-numbers, I believe.
You can change the breakdown, too, such as setting one for numbers
above 365 to a different color. I asked some questions about that
roughly two weeks ago here and got some good answers. Maybe that
article or thread is still on your server. See, for example,

BTW, is there any Help text that explains the Custom format
syntax? I did not find anything myself when I searched for
"custom format". However, I did not look at Online Content. (I
usually disable that feature.)

I don't know, but probably there would be some Microsoft Knowledge
Base stuff about it. Good luck!

-dman-