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.

 

[FormR]

That didn't work unfortunately.

Examples and/or details of why it didn't work would help to find a remedy - but if you are happy to go with what you have.

think I will just stick to using Verdana and using "000" in the text string.

 

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

Sorry, I should have included why it didn't work.

Using your formula, this is what I got when using Verdana...

[TABLE="width: 279"]
<TBODY>[TR]
[TD]Number 1 Number 134</SPAN>
[/TD]
[/TR]
[TR]
[TD]Number 345 Number 121</SPAN>
[/TD]
[/TR]
[TR]
[TD]Number 145 Number 1</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

It didn't align the numbers.

Thanks in advance.

 

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

Please ignore previous table, this one better depicts the results.

Sheet1

	G
Number 1 Number 134
Number 345 Number 121
Number 145 Number 1

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 278px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula G12 =CONCATENATE("Number ",RIGHT(" "&B4,3)," Number ",RIGHT(" "&C4,3)) G13 =CONCATENATE("Number ",RIGHT(" "&B5,3)," Number ",RIGHT(" "&C5,3)) G14 =CONCATENATE("Number ",RIGHT(" "&B6,3)," Number ",RIGHT(" "&C6,3)) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[FormR]

this one better depicts the results.

I think the forum software wacked some of the spaces - there should be 3 spaces in between each set of double quotes.

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

 

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

This is the result although for some reason it won't show the figures out of alignment!!!

Excel 2007

	G
Number 1 Number 134
Number 345 Number 121
Number 145 Number 1

<TBODY>
[TD="align: center"]19
[/TD]

[TD="align: center"]20
[/TD]

[TD="align: center"]21
[/TD]

</TBODY>

Sheet1​

Thanks in advance.

Excel 2007

	G
Number 1 Number 134
Number 345 Number 121
Number 145 Number 1

<TBODY>
[TD="align: center"]19
[/TD]

[TD="align: center"]20
[/TD]

[TD="align: center"]21
[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]G19
[/TH]
[TD="align: left"]=CONCATENATE("Number ",RIGHT(" "&B4,3)," Number ",RIGHT(" "&C4,3))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]G20
[/TH]
[TD="align: left"]=CONCATENATE("Number ",RIGHT(" "&B5,3)," Number ",RIGHT(" "&C5,3))
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]G21
[/TH]
[TD="align: left"]=CONCATENATE("Number ",RIGHT(" "&B6,3)," Number ",RIGHT(" "&C6,3))
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

 

[FormR]

This is the result although for some reason it won't show the figures out of alignment!!!

What font are you using?

 

[Rick Rothstein]

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?

If the size of your Verdana text is between (and including) 10 to 14, then this might work for you (it works on my monitor)...

=CONCATENATE("01 = (M) ",TEXT('Main Table'!E8,REPT("_0",6-2*LEN('Main Table'!E8))&"0")," - (B) ",TEXT('Bonus Table'!E8,REPT("_0",6-2*LEN('Bonus Table'!E8))&"0")," - (Both) ",TEXT('Main & Bonus Table'!E8,REPT("_0",6-2*LEN('Main & Bonus Table'!E8))&"0"))

If it does not work on your monitor, try playing with the numbers and zeroes that I highlighted in red (keeping each set the same).

 

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

What font are you using?

That was my fault, I tested your formula on a different part of the SpreadSheet which was Verdana.
I set the Font to Kaiti and it worked great.

If it does not work on your monitor, try playing with the numbers and zeroes that I highlighted in red (keeping each set the same).

Thanks Rick, I had a play around but could not get it right within my SpreadSheet using Verdana and Font Size 10, 11 etc.

 

[Rick Rothstein]

Thanks Rick, I had a play around but could not get it right within my SpreadSheet using Verdana and Font Size 10, 11 etc.

Yeah, I think it may have something to do with the monitor resolution. I know you have a solution using a different font, and that is good, but I wanted to share how I went about getting those numbers in the first place. I placed "000<" in one cell and "<" in the cell below it and made both cells the desired font name and font size. I then double clicked into the cell with the lone less than symbol, put the cursor in front of the symbol and type spaces until the less than symbols lined up (or came as close to lining up as possible). If you cannot get them to line up to your satisfaction, try changing the 0's in the first cell to some other character and try typing a different number of spaces in the cell below. What you are trying to do is line the less than symbols as much as possible. When I typed in the spaces using the zeros, I got twice as many spaces as zeros to force the lining up... your first goal should be to use a different character (three of them) in place of the three zeros so that the 6 and 2 would not have to be changed, only the 0 would need changing to that character. If you cannot find such a character, then you would need to do play with the 6 and 2 to get the ratio correct.