Lining up figures when using the "=CONCATENATE" function.

[S.H.A.D.O.]

Good evening,

Does anyone know how I can line up 3 sets of figures used in a CONCATENATE function please?

My formula is:

=CONCATENATE("01 = (M) ",TEXT('Main Table'!E8,"0")," - (B) ",TEXT('Bonus Table'!E8,"0")," - (Both) ",TEXT('Main & Bonus Table'!E8,"0"))

It produces this for example:

01 = (M) 2 - (B) 150 - (Both) 2
02 = (M) 3 - (B) 67 - (Both) 3
03 = (M) 16 - (B) 47 - (Both) 16

I want it to produce the figures so they are aligned right for three figures if that make sense.
So the 3 would move right so it is aligned with the 6 below, and the 67 would move right so it is under the 50 of the number 150.
I just want it based on 3 figures really but not show a zero like 002 for example.

I have tried many different formats including the ? in the formula like "?0" but that doesn't work.

Any ideas please?

Thanks in advance.

 

[S.H.A.D.O.]

Sorry time run out before I could edit.

It is like using "000" but hiding the zeros at the FRONT ONLY.

Thanks in advance.

 

[Tetra201]

Try using

TEXT(your_reference,"??0")

and applying a fixed-width font, e.g., Courier New.

 

[S.H.A.D.O.]

Thanks for the reply, it is appreciated.

That does work but unfortunately I was hoping to still use Verdana.

Is there possibly another way to achieve this?

 

[S.H.A.D.O.]

I still can't find an answer to this.

I basically want to format the numbers in the text string as "000" but want to leave the zeros but hide them or make the leading zeros white in colour.

Has anyone got any ideas please if this can even be done?

Thanks in advance.

EDIT:
I suppose this varies and can't be corrected because the width of the space varies according to the width of the character that you choose using the font Verdana for example. That is why Tetra201 suggested using a fixed-width font?

 

[S.H.A.D.O.]

To simpify the formula I am using this as a test case with the font MS Reference Sans Serif which is a fixed-width font:

=CONCATENATE("Number ",TEXT(B4,"000"),"   Number ",TEXT(C4,"000"))

This works very well.
How can I get it to colour the leading zeros WHITE please?
I have tried:

=CONCATENATE("Number ",TEXT(B4,"[WHITE]000"),"   Number ",TEXT(C4,"[WHITE]000"))

...but that doesn't work!

Thanks in advance.

 

[S.H.A.D.O.]

I have tried different combinations of commas, colons, hashes, question marks etc but to no avail.

 

[S.H.A.D.O.]

After me failing to be able to resolve this I think I will just go with what I have.

 

[FormR]

the font MS Reference Sans Serif which is a fixed-width font:

I don't think it is.

How can I get it to colour the leading zeros WHITE please?
I have tried:

You can't partially format the result of formula, but you could try something like this instead.

=CONCATENATE("Number ",RIGHT(" "&B4,3)," Number ",RIGHT(" "&C4,3))

 

[S.H.A.D.O.]

Thanks for the reply, it is appreciated.

=CONCATENATE("Number ",RIGHT(" "&B4,3)," Number ",RIGHT(" "&C4,3))

That didn't work unfortunately.
I think I will just stick to using Verdana and using "000" in the text string.
It's just a shame that I can't apply White to the leading zeros only.

Thanks again.