Phone book formatting

[Jim Berglund]

How do I create a format that will insert the appropriate number of dots
between the name and the phone number so that I'll have the name
left-aligned and the phone number right-aligned, as in the folllowing
example:

Anthony Hopkins............942-4567 (35 characters)

Thanks,
Jim Berglund

 

[Office_Novice]

A few questions...

1) How is the name entered manually of progammaticly
2) specific Range or no?
3)How is the phone number entered?

 

[Jim Thomlinson]

Is this all supposed to be in 1 cell? Is the source data in seperate cells?
Is it always supposed to be 35 characters in length which implies you are
using a non-proportional font like courier?

 

[Dave Peterson]

If you're doing this to create a mini-phonebook, you may find that you have lots
more formatting choices if you did it in MSWord.

 

[Office_Novice]

Assuming Names and phone number a entered manually try this

Option Explicit

Sub AddDots()
Dim Rng As Range
Dim i As Variant

Set Rng = Range("A1:A65536")
i = Cells

For Each i In Rng
i.Offset(1, 0).Select
If i.Value <> "" Then
i.Offset(0, 1).Select
ActiveCell.Value = "..................................."
ElseIf i.Value = "" Then
End If
Next
End Sub

 

[Joel]

I would add another columns with the dots. Then use a formula like this

=Rept(".",35 - len(A10))

where A10 is the cell wi the person name. Rept will repeat the dot x number
of times.

 

[Dave Peterson]

I'm not sure that the OP wanted a separate cell to show the dot leader.

 

[Jim Berglund]

Thanks all. Joel certainly had the simplest solution. Problem solved!
Jim

 

[Jim Thomlinson]

Note that that solution only works with a non-propotional font like courier.
With Arial and other proportional fonts each character is a different width
and 35 characters will take up different amount so space.

 

[Jim Berglund]

Thanks, all of you.
I liked Joel's response, best - short, easy, and it works!
Jim

 

[Dave Peterson]

I don't know.

Using MSWord is pretty simple for tables like this. Changing the formatting for
the tab character is pretty simple, too.