space needed between number& right side of cell

[Jack B]

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]

Maybe you could use a custom format of:

#,##0.00" "

(or some variation)

 

[Gord Dibben]

Format>Cells>Alignment>Horizontal>Right Indent.

Set to 1


Gord Dibben MS Excel MVP

 

[Jack B]

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


Maybe you could use a custom format of:

#,##0.00" "

(or some variation)

 

[Jack B]

Gord,

That is the first thing I tried. However, when you
Format>Cells>Alignment>Horizontal>Right 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
Format>Cells>Alignment>Horizontal>Right Indent.

Set to 1


Gord Dibben MS Excel MVP

 

[Dave Peterson]

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

 

[Ron Rosenfeld]

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]

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]

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

 

[Jack B]

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

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

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

 

[Jack B]

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



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

 

[Dave Peterson]

Yes. That's what I'm saying.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
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.)

 

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