Cell padding/widths for back-of-book index
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Cell padding/widths for back-of-book index

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com