Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding preceeding and following "-" to center text

    I seem to recall in a spreadsheet program long ago the ability to precede a text string and follow it with multiple "-" characters in order to cause the text string to be centered across the columns it pertained to. This would be like the Excel "Center Across Selection" with dashes before and after the text. For example, in Excel 2003 how do I format "Green Impact" so it looks like "--------Green Impact--------" as in the sample below.

    --------Green Impact--------
    # Cars Fuel Burned CO2
    200 800 127.2
    180 730 101.8

    Thanks!

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Interesting problem.

    In a brute-force approach, I came up with this:

    =REPT("-",INT(0.5*((1.3*CELL("width",B2))-LEN(B2))))&B2&REPT("-",INT(0.5*((1.2*CELL("width",B2))-LEN(B2))))

    I put the initial text, Green Impact, in B2 and inserted a new row at 3 (above the other text) where I recreated the one you wanted.
    I think it's close.

    There must be a more elegant way to do this.

    I see the issue being that CELL will give you the points and not the pixels. I think the conversion is 5+points*7 =
    Last edited by kweaver; 2013-04-04 at 19:43.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    What's wrong with setting the alignment of the merged cell to Center?
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I think MCSTORYAU wants to insert a sufficient number of "-" s before and after the text to have it fill the cell based on the max width already set. Maybe I'm missing something, but that's what I thought.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Unfortunately I don't believe there is a format for that. You can use *-@ to pad the left side of the cell with - or you can use @*- to pad the right side but it won't do both.
    You could use borders instead or a fill.
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

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