Cell padding/widths for back-of-book index

[ChandlerL]

Hi all,

I'm attempting to create a back-of-book index in Excel.
This means that I want to have my text looking like this:

"aardvark........................69" meaning 'aardvark is found on page 69"
"bear............................70" meaning 'bear is found on page 70"
"cat.............................72" etc..

Now, in my case I actually have 3 relevant columns since I need to put an asterisk in between some of the index categories but that’s just trivial information.

So ultimately I want to have this:
"aardvark...................*....69"
“bear............................70”
“cat........................*....72”

Basically, your standard book index.

Now I found the life saving formatting option of "*@." and "*.@" which pads a cell with periods from the left to the right and vice versa, respectively.

This SHOULD give me what I need.

Here's the problem:

What I GET is this:

"aardvark............... ... ....69"
"bear................... .*. ....70"
"cat.................... ... ....72"

It seems the Excel cells have some sort of gutter width so the periods don't meet up with each other. Is there anyway to remedy this so the periods adjoin each other properly?

-Chandler "Frustrated & Desperate" L.

 

[Mark W.]

Suppose, A1:B3 contains...

{"aardvark",69
;"bear",70
;"cat",72}

You could enter the formula...

=A1&REPT(".",20-LEN(A1))&REPT(".",LEN(MAX(B:B))-LEN(B1))&B1

...into C1 and copy down to C3. Change the first REPT function's constant (i.e., 20) to suit your needs. Format column C with a non-proportional font (e.g., Courier). Hide columns A:B.
This message was edited by Mark W. on 2002-02-21 10:16

 

[ChandlerL]

Jackpot!

You're a lifesaver. I took your formula and with very small modifications which allowed me to right justify the other sections of the index, it worked like a charm.

Here's is the completed formula if anybody is interested:

=Index1!A4&REPT(".",18-LEN(Index1!A4))&REPT(".",12-LEN(Index1!C4))&Index1!C4&IF(Index1!D4="@","@",".")&REPT(".",6-(LEN(Index1!E4)))&Index1!E4

The above formula generates the following results for by back-of-book index:

"AQP63.1.................401.00@....98"
"ARG62.101................38.50@.....8"
"ARG64...................525.00.....99"

...with the small changes the prices and the part numbers are right justified. I love it that there are smarter people out there than myself.

Thanks!

-Chan

P.S. This message board utilizes a proportional spaced font so the above examples don't line up, but in actual practice and in using a fixed width font, everything lines up perfectly.
This message was edited by ChandlerL on 2002-02-25 13:58