Trailing Periods (Dot Leaders)

[Guest]

Is there any way to add the trailing periods, dot leaders to cells that
contain text and in the next cell a dollar amount, I have someone requesting
it so that it is easier for them to see which amount corresponds to that
description. If you manually insert them, you have to keep messing with how
many periods to type to line up to the next cell. Then when you make text
changes you have to play around with it again. Then, depending on your
printer, it prints differently and doesn't always line up.

 

[Gord Dibben]

Format>Cells>Number Tab>Custom @*.

Or a macro you can add to a button or shortcut.

Sub Leaders()
Selection.NumberFormat = "@*."
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

In addition the @* and the next character will fill-in the cell, this @*-
would fill in dashes and @*> would be the > characater.

 

[Sandy Mann]

Michelle,

When a post goes unanswered it usually means that either what is asked is
not possible or it has not been described sufficiently. Your request is
clear enough so I assume that, although it is possible to do what you want
with numbers, it is not possible with text. Here is a workaround using an
event Macro:

Highlight Column F (or whatever column your currency will be in), and custom
format it as "*.£#,##0.00" without the quotes and using whatever currency
symbol you use.

Right-click on the sheet tab and select "View Code" copy and paste this
macro in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
'Replace E:E with your own description column if different

If Target.Rows.Count > 1 Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub

Application.EnableEvents = False

Target.Value = Target.Value & _
Application.Rept(".", 20 - Len(Target.Value) * 2)
'Increase 20 to a greater number if your column is
'wider than the standard 8.43
Cells(Target.Row, Target.Column + 1).Value = 0

Application.EnableEvents = True

End Sub

The excess periods in column E will be hidden because the cell in column F
has an entry. I assume that you have the gridlines turned off, if so there
will be a slight gap where the gridline would have been.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

Sandy

I answered this post 8/28/2007

OP did want to add leaders to numbers, just the text in the column adjacent to
the numbers.


Gord

 

[Sandy Mann]

So I see on Google...... damned Outlook Express!

I see also that Reno said that a custom format of @*. would fill the text
cell with dots. Funny I could have sworn that I tried that format before I
posted but obviously not.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk