Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having to
hit the spacebar after each number. How do you do that?

Jack

Maybe you could use a custom format of:

#,##0.00" "

(or some variation)

Jack B wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having to
hit the spacebar after each number. How do you do that?

Jack

--

Dave Peterson

FormatCellsAlignmentHorizontalRight Indent.

Set to 1


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 16:46:57 -0400, "Jack B"
wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having to
hit the spacebar after each number. How do you do that?

Jack

Dave,

Okay, that works. Too bad you just can't enter a border spacing for cells
or a right indent for a bunch of cells. The only draw back with the
#,##0.00" " is that it doesn't work the same for all cells. That is if some
cells have different types of entries. Even Bold numbers do not shift to
the left the same distance.

Thanks.

Jack
---------------------------------------


"Dave Peterson" wrote in message
...
Maybe you could use a custom format of:

#,##0.00" "

(or some variation)

Jack B wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having
to
hit the spacebar after each number. How do you do that?

Jack

--

Dave Peterson

Gord,

That is the first thing I tried. However, when you
FormatCellsAlignmentHorizontalRight then Indent (enter a number), the
Right automatically changes to Left, and you can't get a Right Indent. At
least it won't do it for me.


Jack
-------------------------------------


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
FormatCellsAlignmentHorizontalRight Indent.

Set to 1


Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 16:46:57 -0400, "Jack B"
wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having to
hit the spacebar after each number. How do you do that?

Jack

xl2003 let me use Gord's technique.

Can you change the number, then change the direction?

I'm not sure why you can't change this, but maybe you could use a macro:

Option Explicit
Sub ChangeIndent()
With Selection
.HorizontalAlignment = xlRight
.IndentLevel = 1
End With
End Sub

Select a group of cells, run the macro and see what happens???

Jack B wrote:

Gord,

That is the first thing I tried. However, when you
FormatCellsAlignmentHorizontalRight then Indent (enter a number), the
Right automatically changes to Left, and you can't get a Right Indent. At
least it won't do it for me.

Jack
-------------------------------------

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
FormatCellsAlignmentHorizontalRight Indent.

Set to 1

Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 16:46:57 -0400, "Jack B"
wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having to
hit the spacebar after each number. How do you do that?

Jack


--

Dave Peterson

On Fri, 31 Oct 2008 01:29:14 -0400, "Jack B"
wrote:

Dave,

The only draw back with the
#,##0.00" " is that it doesn't work the same for all cells. That is if some
cells have different types of entries. Even Bold numbers do not shift to
the left the same distance.


Could you give some examples of not working the same?

I entered a variety of numbers in both bold and normal font, and they all
seemed to shift the same from the right margin.
--ron

If you change the font size or even the font, you may not see the same "indent".

But in my simple tests, using the indent on the alignment tab worked fine.

Ron Rosenfeld wrote:

On Fri, 31 Oct 2008 01:29:14 -0400, "Jack B"
wrote:

Dave,

The only draw back with the
#,##0.00" " is that it doesn't work the same for all cells. That is if some
cells have different types of entries. Even Bold numbers do not shift to
the left the same distance.


Could you give some examples of not working the same?

I entered a variety of numbers in both bold and normal font, and they all
seemed to shift the same from the right margin.
--ron

--

Dave Peterson

On Fri, 31 Oct 2008 07:50:04 -0500, Dave Peterson
wrote:

If you change the font size or even the font, you may not see the same "indent".

But in my simple tests, using the indent on the alignment tab worked fine.

Concur.

--ron

Okay, I see what the deal is.

The Bold indent shifts more left (only on the screen -- it prints with
correct alignment) if not viewed at 100%. For instance, with the view set
at 75%, the bold numbers are considerably more shifted to the left on my
monitor.

The cells that have numbers expressed as percentages get reduced to 0 when
applying the #,##0.00" " -- not that that's not understandable, it's just
impossible to apply #,##0.00" " to the whole spreadsheet without causing
problems if there's a variety of entry types.

Again, it would be nice to have an indent capability like in a Word text box
and even in a Word table (space between columns).

Thanks.

Jack

----------------------------------




"Ron Rosenfeld" wrote in message
...
On Fri, 31 Oct 2008 07:50:04 -0500, Dave Peterson

wrote:

If you change the font size or even the font, you may not see the same
"indent".

But in my simple tests, using the indent on the alignment tab worked fine.

Concur.

--ron

Dave,

Can you change the number, then change the direction?<

If you enter a number and then select Right, the number auto reverts to 0.

Are you saying below that you can do the Right indent in Office 2003?

Man, it's been so long since I've done a macro in Excel, I forget how to
apply it.


Jack
-----------------------------------------



"Dave Peterson" wrote in message
...
xl2003 let me use Gord's technique.

Can you change the number, then change the direction?

I'm not sure why you can't change this, but maybe you could use a macro:

Option Explicit
Sub ChangeIndent()
With Selection
.HorizontalAlignment = xlRight
.IndentLevel = 1
End With
End Sub

Select a group of cells, run the macro and see what happens???

Jack B wrote:

Gord,

That is the first thing I tried. However, when you
FormatCellsAlignmentHorizontalRight then Indent (enter a number), the
Right automatically changes to Left, and you can't get a Right Indent. At
least it won't do it for me.

Jack
-------------------------------------

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
FormatCellsAlignmentHorizontalRight Indent.

Set to 1

Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 16:46:57 -0400, "Jack B"

wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having
to
hit the spacebar after each number. How do you do that?

Jack


--

Dave Peterson

Yes. That's what I'm saying.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Jack B wrote:

Dave,

Can you change the number, then change the direction?<

If you enter a number and then select Right, the number auto reverts to 0.

Are you saying below that you can do the Right indent in Office 2003?

Man, it's been so long since I've done a macro in Excel, I forget how to
apply it.

Jack
-----------------------------------------

"Dave Peterson" wrote in message
...
xl2003 let me use Gord's technique.

Can you change the number, then change the direction?

I'm not sure why you can't change this, but maybe you could use a macro:

Option Explicit
Sub ChangeIndent()
With Selection
.HorizontalAlignment = xlRight
.IndentLevel = 1
End With
End Sub

Select a group of cells, run the macro and see what happens???

Jack B wrote:

Gord,

That is the first thing I tried. However, when you
FormatCellsAlignmentHorizontalRight then Indent (enter a number), the
Right automatically changes to Left, and you can't get a Right Indent. At
least it won't do it for me.

Jack
-------------------------------------

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
FormatCellsAlignmentHorizontalRight Indent.

Set to 1

Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 16:46:57 -0400, "Jack B"

wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having
to
hit the spacebar after each number. How do you do that?

Jack


--

Dave Peterson

--

Dave Peterson

Try it in a test worksheet with a cell that's has a General number format.

If it works there, but doesn't work in your real worksheet, what is the number
format of the cell with the problem?

Dave Peterson wrote:

Yes. That's what I'm saying.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Jack B wrote:

Dave,

Can you change the number, then change the direction?<

If you enter a number and then select Right, the number auto reverts to 0.

Are you saying below that you can do the Right indent in Office 2003?

Man, it's been so long since I've done a macro in Excel, I forget how to
apply it.

Jack
-----------------------------------------

"Dave Peterson" wrote in message
...
xl2003 let me use Gord's technique.

Can you change the number, then change the direction?

I'm not sure why you can't change this, but maybe you could use a macro:

Option Explicit
Sub ChangeIndent()
With Selection
.HorizontalAlignment = xlRight
.IndentLevel = 1
End With
End Sub

Select a group of cells, run the macro and see what happens???

Jack B wrote:

Gord,

That is the first thing I tried. However, when you
FormatCellsAlignmentHorizontalRight then Indent (enter a number), the
Right automatically changes to Left, and you can't get a Right Indent. At
least it won't do it for me.

Jack
-------------------------------------

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
FormatCellsAlignmentHorizontalRight Indent.

Set to 1

Gord Dibben MS Excel MVP

On Thu, 30 Oct 2008 16:46:57 -0400, "Jack B"

wrote:

Re Excel 97 ---

I would like to format a bunch of cells so that when I enter numbers they
will not be flush against the right side of the cell -- I want to have a
space between the numbers and the right side of the cells without having
to
hit the spacebar after each number. How do you do that?

Jack


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson