Excel Numbers: Million Round to Thousand; Hundreds to decimals; 0 show as -

I want to use the excel custom formating to show the following numbers

1,500,000  show as 1,500 note: no decimals
-1,500,000 show as (1,500) note: no decimals
150,000 show as 150 note: no decimals
-150,000 show as (150) note: no decimals
150  show as .15 note: two decimals
-150 shos as (.15) note: two decimals
0 show as "-" note: without quotation and no decimals

I would like to combine all these options under the custom formating of excel. Don't want to use formula like round function.
PachecdaAsked:
Who is Participating?
 
TommySzalapskiCommented:
Sorry. I meant
#,##0.#, ;(#,##0.#,);-;
Didn't read your post too well.
0
 
Michael FowlerSolutions ConsultantCommented:
As far as I am aware this cannot be done with formatting. I think you will have to use a formula or even a macro.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
PachecdaAuthor Commented:
I was hoping to use the operators like [<=> 1000], #,##, in the custom formating portion, but I guess is not doable. I use nvision to create financials and I really cannot use formula or even a macro since they populate automatically with little user intervention.
0
 
PachecdaAuthor Commented:
Hi Zopilote:

I look at your suggestion, but it does not exactly answers what I want in my description above.
0
 
TommySzalapskiCommented:
Why not use this custom format?
#,##0.00, ;(#,##0.00,);0;
0
 
TommySzalapskiCommented:
Or this to dodge the leading 0s and allow up to three decimal places
#,###.###, ;(#,###.###,);-;
0
 
PachecdaAuthor Commented:
Hi Tommy;

I like your idea, but is there a way to pefect it even further. After pluggin #,##0.#, ;(#,##0.#,);-; as my custom format, I got:

Tommy             Optimal
864.3                     864
    2.                           2
    0.1                      0.1
If you can get my optimal to work, would be great. I only care about the decimal when is between 0 an 1000. Greater than that will round to thousand


0
 
TommySzalapskiCommented:
What version of Excel do you have?
0
 
TommySzalapskiCommented:
If you have 2007 you can use conditional formatting. Select the entire sheet and set a rule
formula =abs(A1)>=1000 then set the number format to #,###, ;(#,###,);-;
And the other case would just use the original format.
0
 
PachecdaAuthor Commented:
Sorry Tommy, I have version 2003.
0
 
TommySzalapskiCommented:
If you have 2000/2003 you could do it in VBA, but I doubt you want to add macros just for that.
0
 
TommySzalapskiCommented:
One other option would be
[>1000]#,###,;[<-1000]-#,###,;#,###.###,
This will display the numbers as you want, but will show negatives with - instead of () and 0 as . not -
This is as close as you can get in 2003 without macros.
0
 
PachecdaAuthor Commented:
Hi Tommy:

I was trying to move away from a macro, but I can have that macro run when the spreadsheet is open to see if we can get to the desire results. All of your options you presented were good and I like them, but they were not just the one I was looking for. However, thanks for the try. Do you think you can try VBA code.

Thanks.
0
 
TommySzalapskiCommented:
This code would do it for the whole sheet
Dim r As Range

For Each r In Sheets("Sheet2").UsedRange
  If IsNumeric(r.Value) Then
    If Abs(r.Value) < 1000 Then
      r.NumberFormat = "#,###.###, ;(#,###.###,);-;"
    Else
      r.NumberFormat = "#,###, ;(#,###,);text;"
    End If
  Else
    r.NumberFormat = "General"
  End If
Next

Open in new window

0
 
TommySzalapskiCommented:
Run that once and then add this code to the worksheet so it doesn't look at the whole thing every time.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

For Each r In Target
  If IsNumeric(r.Value) Then
    If Abs(r.Value) < 1000 Then
      r.NumberFormat = "#,###.###, ;(#,###.###,);-;"
    Else
      r.NumberFormat = "#,###, ;(#,###,);text;"
    End If
  Else
    r.NumberFormat = "General"
  End If
Next
End Sub

Open in new window

0
 
PachecdaAuthor Commented:
Tommy you are the best, you have done. Thanks much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.