Excel FORMAT function can\'t have both commas and space padding? Can anyone advi

Question

Excel FORMAT function can't have both commas and space padding?   

Can anyone advise me - I would like to have comma separation for thousands, as well as space padding to achieve "right justified" numbers (right aligned). These are going into long text strings, and the strings are output one after the other, so numbers are "right aligned" into columns vertically.

But please, the question is strictly about the FORMAT function - not about other ways to align or present text. I'm asking about this odd thing with FORMAT I am having trouble with.

The online Help for FORMAT says use @ for space padding and put in a comma for comma separation. I can get either to work okay, separately. I just can't get them to work together. Also throw into the mix the possible use of # and 0 and I don't know if I'm missing something, or what. Here's the problem result:

Format(1234,"@@@@@#,#") returns " 1234#,#"

Format(1234,"@@@@@0,0") returns " 12340,0"

etc.

Neither is what I actually want, " 1,234".

Am I doing anything wrong, or have I pushed it too far?

If you are unsure, please try your answer before replying - it seems very tricky to me! Like the function breaks down.

Thanks if you can help!

Explanation / Answer

@ is for text strings ONLY, not for numbers. That's why combining @ with # or, doesn't work.

Let's say you want to create a string of length 6, left-padded with spaces if necessary. You can use the following expression for this:

Right(Space(6) & Format(1234, "#,##0"), 6)

Warning: if Format(n, "#,##0") returns more than 6 characters, the above expression will truncate the number from the left hand side. For example

Right(Space(6) & Format(1234567, "#,##0"), 6)

will result in

34,567

because only the last 6 characters are returned. It's your responsibility to reserve enough characters for the values you want to display.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.