Custom Format Placeholder

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

Unless you use a fixed width font, no placeholder will work since letters are shown in different widths.

Reply | Quote

Can’t you simply left-align the cells ?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Format them as:
0_X;-0_X_X;0_X;@
and that should work for single digit numbers.

Reply | Quote

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.

Reply | Quote

I don’t think you can do this with a simple number format with centre alignment.

Reply | Quote