Adding comma to custom number

[mcook]

I am using Excel 2010 and having a problem figuring this out. I have a number like 123456, i needed to add a decimal to the last 2 digits. I used #"."#0 in the Custom types, now i need to add the comma so the number shows as 1,234.56. Can anyone help?

 

[Majawat]

I believe it would therefore be: #,##0.00

Excel also has this built in. In Format Cells, under Number. Make sure "Use 1000 Separator (,)" is checked and Decimal places shows 2.

Alternatively, on the Home tab of the ribbon in the Number section, click on the comma symbol and use the Increase Decimal button to give it the 2 decimal places.

 

[mcook]

That adds zeros to the end of the number.

 

[Majawat]

Oooh. Good point, I had missread what you originally asked.

This worked for me: #","###"."#0

 

[mcook]

Hmm, that worked for the most part. How do i keep the comma out if the number is not in the thousand range. Example ,876.54 should look like 876.54

Thanks..

 

[Rick Rothstein]

I have a number like 123456, i needed to add a decimal to the last 2 digits. I used #"."#0 in the Custom types

This worked for me: #","###"."#0

@mcook,

Just checking... you do realize you are only changing the appearance of your number, not its actual value, right? If you plan to use this number in calculations, while the cell shows 1,234.56, the value that will be used in your calculations is still 123456.

 

[Rick Rothstein]

Hmm, that worked for the most part. How do i keep the comma out if the number is not in the thousand range. Example ,876.54 should look like 876.54.

With this assumption that you read my previous comments, give this Custom Format pattern a try...

[>99999]#","###"."#0;General

 

[mcook]

Thanks.. Yes, i do realize that..

 

[mcook]

When i use that custom formula, any number that is not in the thousands does not have the decimal. All the numbers are bunched together.
Like this..
<TABLE style="WIDTH: 86pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=114><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 86pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=114 align=right>2,270.97</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 align=right>83463 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 align=right>1,062.58</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 align=right>1,580.57</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 align=right>80790 </TD></TR></TBODY></TABLE>

 

[mcook]

With this assumption that you read my previous comments, give this Custom Format pattern a try...

[>99999]#","###"."#0;General

Thanks for help by the way..