Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Custom Format Placeholder

    I'm using Excel 2013 on Windows 8.1.

    I'd like a placeholder that will show a blank to the left of the decimal, but to the right of the digits.

    What I want is to be able to line up 3a and 4 over the digits.

    So it looks like this:

    3a
    4

    And not like this:

    3a
    4

  2. #2
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Sorry ... that looked good in the composition window, but not so good when posted.

    My columns will be centered.

    I want the digits to line up like this:

    3a
    4

    But what I get is the 4 shifted a bit to the right so that the 3 and 4 don't line up vertically. I've represented that little bit of offset with an "i":

    3a
    i4

    It seems to me that I need a custom format with an invisible placeholder to the right of the 4, shown here with an "x":

    3a
    4x

    What placeholder do I use to get this effect?

  3. #3
    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
    Unless you use a fixed width font, no placeholder will work since letters are shown in different widths.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. The Following User Says Thank You to rory For This Useful Post:

    boobounder (2014-09-24)

  5. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Can't you simply left-align the cells ?

  6. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Thanks Rory: yes, I use a fixed-width font in Excel. So is there a placeholder that will work?

    MartinM: Yes, of course I could left-align the cells ... but the whole reason for asking the question is that in this application I want them centered.

    Anyone else have any ideas?

  7. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    In the cell format dialog select Left (Indent) and in the Indent: box choose how far from the left you want the characters to be.

  8. The Following User Says Thank You to MartinM For This Useful Post:

    boobounder (2014-09-24)

  9. #7
    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
    Format them as:
    0_X;-0_X_X;0_X;@
    and that should work for single digit numbers.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Thanks MartinM: this workaround will do what I need.

    But I'm intrigued by Rory's suggestion. This definitely works for 2 digits. But I'm not sure how to get it to work for 3 digits. For example, what if I want to have this centered over the first digits:

    1
    2b
    3a2

    I can't get it to work properly with that optional last digit on the right.

  11. #9
    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
    I don't think you can do this with a simple number format with centre alignment.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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