Formatting numbers in TEXT

[lotakov]

I use this formula:
=" "&TEXT(COUNTIF(G5:G22,"appr"),"000")&" "&TEXT(COUNTIF(I5:I22,"appr"),"000")&" "&TEXT(COUNTIF(G5:G22,"appr")+COUNTIF(I5:I22,"appr"),"000")
to display numbers in a merged cell.
The "000" formatting seems to be the only one which keeps positions intact, but it displays leading zeroes, so I have it as " 001 023 004" etc... Is there a formatting option to display blanks in place of zeroes, but keeping number position as it does with "000", so it would look like: " ^^1 ^23 ^^4", etc..?
Thank you very much.

 

[pgc01]

Hi
Welcome to the board

Yes, you can use the ?, like in

=TEXT(A1,"??0")

... but, since in most fonts the caracters have variable width you may not get the result you want, unless you use a monospaced font, like Courier or Courier New.

Please try.

 

[lotakov]

Thank you, PGC.
Unfortunately, this does not work.
It shifts the sugnificant digit and disturbs entire alignment.
What I need is leading zeroes replaced by equal number of leading spaces, so the sugnificant digit(s) does not move.

 

[pgc01]

Hi

As I posted, I believe that in this case it will only work if you use a monospace font.

Try the following. Use format I posted in some cells and then change their font to Courier New.

 

[lotakov]

Hi

As I posted, I believe that in this case it will only work if you use a monospace font.

Try the following. Use format I posted in some cells and then change their font to Courier New.

Hi, PGC.

Fascinating! Looks like this will work! What are monospace fonts? I am using Courier and it looks good.

Thank you very, very much.
Yuri.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

Another one you may try is Lucida Console.