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?

 

[Rick Rothstein]

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>

Sorry, I forgot to check for that. Try this instead...

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

 

[Majawat]

Try [>99999]#","###"."#0;[>1000]###"."#0

 

[mcook]

@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.

What would be the alternative? How do i go about changing the value of the field to be 1,234.56?

 

[mcook]

Try [>99999]#","###"."#0;[>1000]###"."#0

Much closer.... now zero "0" value shows as ######################

Thanks

 

[Rick Rothstein]

Much closer.... now zero "0" value shows as ######################

It wasn't clear how small a number you wanted to be able to handle. Here is my Custom Format pattern, modified to better handle small values...

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

 

[mcook]

It wasn't clear how small a number you wanted to be able to handle. Here is my Custom Format pattern, modified to better handle small values...

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

Excellent.. Thank you..

 

[Rick Rothstein]

What would be the alternative? How do i go about changing the value of the field to be 1,234.56?

You cannot format a change in value... you will have to find a way to divide that number by 100 in order to make it the right magnitude. How you go about this depends on how the number is getting into your worksheet in the first place and whether you need to preserve the original value or not. Assuming you do not want to use VB code, you could put this formula in B1 (assuming the value is in A1)...

=A1/100

then hide Column A (where the real value is stored) and format Column B cells normally. If you don't mind using VB code and/or you want to physically change the value in the cell itself, you will need to tell us where these values are located on your worksheet and how they are getting into your worksheet (manually type, copied from elsewhere, some other method?).

 

[mcook]

I don't think we need to get all that complicated now..lol, but it's good to know.. Thanks for all your help with this..