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?

In .com,
spake thusly:

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-

==================================================
In .com,
spake thusly:

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 wrote:
spake thusly:
What is the best way to offset cell values (text and numbers) from the
cell border lines?

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.

In . com,
spake thusly:

Dallman Ross wrote:

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

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(!).

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.

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)?

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,
Message-ID: ,
where someone named sshankle answered me with some good tips.

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-