Solved Number formatting

Microsoft Microsoft excel 2007 full vers...
June 9, 2011 at 06:00:37
Specs: Windows XP
 In Microsoft excel I need to format my cells to hundreds with a thousands separater and one decimal point whether it is .0 or a number and with a negative being in red. I used this #.#,;[Red](#.#,) which is fine except that it does not do the thousand separater or the .0 for instance if it says 465000 i want 465.0 if it says 4650000 i want 4,650.0 if it says 465125 i want 465.1.. any suggestions..

See More: Number formatting

#1
June 9, 2011 at 08:36:51
 It sounds like you are asking Excel to divide a number by 1000 through the formatting feature. I don't think that's possible.You probably have to use another cell with a formula to divide the original number by 1000 and then format that cell the way you want.Another option is a Worksheet_Change macro that divides the number entered by 1000 as soon as it is entered and then replaces the original number with the new value.Maybe someone who is better with Custom Formatting will have a different idea, but I'm pretty sure you can't perform an operation on a number via any type of formatting.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
June 9, 2011 at 08:47:27
 actually the division by 1000 isnt the problem i manged that. As i said the issue is more that while created this format i need to also have a comma separater for the 1000 and i want the decimal point to show even if it is 0

Report •

#3
June 9, 2011 at 12:26:37
 Try this:#,#00.#,;[Red](#.#,)MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
June 9, 2011 at 12:44:35
 Mike, I tried that one earlier.It works for 4651234, returning 4,651.2 (decimal 2)But if you try it with 4650000 you get 4,650. (decimal blank)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#5
June 9, 2011 at 12:55:22
 perfect i just had to create the same format codes for the red section but this and changed #,#00.# to #,#00.0 and it works perfectlyso it reads:#,#00.0,;[Red](#,#00.0,) thanks much

Report •

#6
June 9, 2011 at 12:56:44
 sorry for my english.. i had two thoughts at once.. but thanks again Mike

Report •