CONCATENATE + LEFT

[Seahorse]

I am using CONCATENATE to generate 3 columns of information in a bar chart axis as this seems to be the only way to have more than the axis title.

I am combining the:

• Task
• Owner
• Basline (date)

This works:

=CONCATENATE(B2," │ ",C2," │ ",(TEXT(E2,"dd mmm yy")))

resulting in:

Discovery │ Bill │ 15 May 14

I can control the date length using formatting to make the date 'column' E2 the same length, however the Owner in C2 is variable and I want to use 15 characters (even if the text is shorter than 15 characters) to make the vertical bars align making it easier to read.

Whilst I can happily use =LEFT(C2,15) on its own, I am struggling to shoehorn it into the formula:

=CONCATENATE(B2," │ ",(LEFT(C2,15)," │ ",(TEXT(E2,"dd mmm yy")))

I have the right number of brackets, the commas look ok, any idea where I am going wrong?

 

[json]

=CONCATENATE(B2," │ ",(LEFT(C2,15))," │ ",(TEXT(E2,"dd mmm yy")))

OR you could get rid of them:
=CONCATENATE(B2," │ ",LEFT(C2,15)," │ ",(TEXT(E2,"dd mmm yy")))

let us know if this still isnt working as desired

 

[Seahorse]

Argh, I knew it was the brackets! Yes that does work, however I am not gettering the behaviour I expected.

LEFT apparently allows a maximum of 15 character, but if the name is Bill it only uses 4. I need all text to use 15 character 'spaces' essentially Bill+11 blank spaces.

 

[amp_82]

Try this

=CONCATENATE(B2," │ ",C2,REPT(" ",15-LEN(C2))," │ ",(TEXT(D2,"dd mmm yy")))

 

[Seahorse]

Sorted, thank you both, clearly I have had to use a Monospaced font, but the working version essentially formats all 3 Columns the first two by characters, the final one by date format.

=CONCATENATE(B2,REPT(" ",24-LEN(B2))," │ ",C2,REPT(" ",10-LEN(C2))," │ ",TEXT(D2,"dd mmm yy"))

 

[Hui]

You will also have to use a Fixed Width Font if you want things to line up properly
eg: Courier, Fixedsys, Modern, MS Sans Serif, Roman etc

 

[Seahorse]

Monospace = Fixed width.

The other caveats are that the font needs to be a standard Windows/Office font and it needs to look as close to Calibri as possible which as far as i can tell is Lucida Console.