Concatenation misaligns the number formatting by decimal

[amit_gupta123]

Hi

I am trying to concatenate the text in ColA with two a number formatted at two decimal place Col B. But the result comes in mis aligned format in Col C. Figuring out how to get the desired returned as given in Col D where the numbers are aligned with decimal

 

[Hui]

Amit

The only way to guarantee the alignment of the characters at the decimal place is to use a Fixed Width Font like Courier, Fixedsys, Modern, MS San Serif, Roman, System etc

Then in C1 I would then use:
=A1&REPT(" ",8-LEN(TEXT(B1,"#000.00"))+1)&TEXT(B1,"#000.00")

Copy down

In your column D if you look closely at the Thousands column you will see that the 8, 5 and 7 aren't aligned, there close, but not aligned.

 

[amit_gupta123]

Thanks

 

[amp_82]

I think this might do what you're after and allows for longer codes than 3 characters

=A1&REPT(" ",20-LEN(A1&B1))&B1

See attached file for an example, everything appears to be lined up correctly. And as Hui said it needs to be a fixed width font, such as courier.