Indenting cells in Excel

[Guest]

We work with standards that have been set for the company. One is that when
creating tables, the indentation of sub-categories of a table must be 5
spaces. The indent option (under Format/Alignment), when set to five, seems
to actually indent 15 spaces. I'm looking for a way to create a template that
we can use as a basis for all tables, is there a way to set the indent option
so that using it actually indents five spaces (as in five space bar spaces)?
Thanks in advance.

 

[Guest]

Hi Beth-

You're definitely using the right feature, and the Help info simply defines
the increment as "1 character". Based on proportional fonts and the variation
in character width from one font to another, that is not very much info to go
on.

This has been a source of frustration for me too. It basically amounts to
guesswork although it is at least consistant for each cell regardless of font
or font size.

Hopefully one of the gurus will address this issue and let us know what MS
considers to be "1 character" in terms of fractions of an inch, points, or
something more finite. |:>)

 

[Gord Dibben]

Beth

Indenting is a crap-shoot at best what with different fonts and resolutions.

Use this macro to indent 5 spaces(not characters). No error-check and if cell
already contains leading spaces, will add to them. Works on both numbers and
text but not cells with formulas.

Sub Format_Indent5()
Dim cell As Range, I As Integer, tempS As String
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not cell.HasFormula Then
If cell.PrefixCharacter = "'" Then
cell.NumberFormat = " @"

ElseIf IsNumeric(cell.Value) Then
cell.NumberFormat = " " & cell.NumberFormat

Else
cell.NumberFormat = " @"
End If
End If
Next
End Sub


The following allows you to choose number of spaces and indenting left or
right.

Sub Indent_Text()
Dim num As Integer, str As String
Dim cell As Range, I As Integer

num = Application.InputBox(prompt:="Enter the number of " & _
"spaces to indent text. If a negative number " & _
"is entered, text will be shifted left that ." & _
"number Truncation may occur. Only text " & _
"entries are affected.", _
Type:=1)
If num = 0 Then
MsgBox "No value entered. Activity halted."
End
End If
If num > 0 Then
For I = 1 To num
str = str & " "
Next
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
If (Not IsEmpty(cell)) And (Not IsNumeric(cell)) And _
(Not Left(cell.Formula, 1) = "=") Then
cell.Value = str & cell.Value
End If
Next cell
Else
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
If (Not IsEmpty(cell)) And (Not IsNumeric(cell)) And _
(Not Left(cell.Formula, 1) = "=") Then
If Len(cell.Value) + num > 0 Then
cell.Value = Right(cell.Value, _
Len(cell.Value) + num)
Else
cell.ClearContents
End If
End If
Next cell
End If
End Sub


Gord Dibben Excel MVP With thanks to Bob Flanagan for the code.