Custom format : Negative for K and M

henrytran

New Member
Joined
Feb 28, 2017
Messages
5
I want to custom format for number fields as follows:

1,234,567= 1.23M
-1,234,567= -1.23M
123,456= 123.46K
-123,456= -123.46K

But I use format [>999999]#.00,,"M";[>999]#.00,"K";#,##0.00; I have the problem
-1,234,567= -1,234,567
-123,456= -123,456

Please advise me if I can set format positive and negative for both M and K.

Thank you.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
One cannot have that many conditions in a custom number format. What you've got there won't work because the semi-colons separate either the conditions or the positive/negative/zero/text parameters. But we can use Conditional Formatting combined with number formatting to achieve your objective.

Put 12345467 in A1 and the other samples in the subsequent rows. Format them all to General number format. Select A1. Invoke the CondFrmt dialogue box using New Rule. Select 'Use a formula...' and then paste this exactly into the "Format values where..." box =A1>0

Then select the "Format" button, go to the "Number" tab and select "Custom". Paste this exactly into the "Type" box [>999999]#.00,, \M;[>999]#.00, k;#,##0.00

Select OK all the way out. Then use that procedure to create a second CondFrmt rule for =A1<0 and use this format in the "Type" box [<-999999]-#.00,, \M;[<-999]-#.00, k;#,##0.00 and select OK all the way out.

Copy A1 and PasteFormat to any other cell that you care to.
 
Last edited:
Upvote 0
sorry forgot about the negative sign

=IF(AND(LEFT(H7,1)="-",LEN(H7)=8),ROUND(H7/1000000,2)&"M",IF(AND(LEFT(H7,1)<>"-",LEN(H7)>=7),ROUND(H7/1000000,2)&"M",IF(AND(LEFT(H7,1)="-",LEN(H7)=7),ROUND(H7/1000,2)&"K",IF(AND(LEFT(H7,1)<>"-",LEN(H7)=6),ROUND(H7/10000,2)&"K",H7))))
 
Upvote 0
One cannot have that many conditions in a custom number format. What you've got there won't work because the semi-colons separate either the conditions or the positive/negative/zero/text parameters. But we can use Conditional Formatting combined with number formatting to achieve your objective.

Put 12345467 in A1 and the other samples in the subsequent rows. Format them all to General number format. Select A1. Invoke the CondFrmt dialogue box using New Rule. Select 'Use a formula...' and then paste this exactly into the "Format values where..." box =A1>0

Then select the "Format" button, go to the "Number" tab and select "Custom". Paste this exactly into the "Type" box [>999999]#.00,, \M;[>999]#.00, k;#,##0.00

Select OK all the way out. Then use that procedure to create a second CondFrmt rule for =A1<0 and use this format in the "Type" box [<-999999]-#.00,, \M;[<-999]-#.00, k;#,##0.00 and select OK all the way out.

Copy A1 and PasteFormat to any other cell that you care to.

Use this not my formula.

@DRSTeele this is some nice stuff here
 
Upvote 0
@henrytran:

Her is another way:

1. Custom-format your target cells as
[>999]#.00,"K";[<-999]-#.00,"K";#,##0.00;

2. Condition-format the same cells using the following rule and format:
=OR(A1>999999,A1<-999999)
#.00,,"M";-#.00,,"M"
 
Last edited:
Upvote 0
You can't do that with straight cell formatting, but you can implement it using event code. Right click the tab at the bottom of the worksheet and select View Code from the popup menu that appears, then copy/paste the following into the code window that opened up...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Pattern As String, TargVal As Double, Cell As Range
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    For Each Cell In Intersect(Intersect(Target, Columns("A")), ActiveSheet.UsedRange)
      If Not Application.IsText(Cell.Value) And Cell.Value <> "" Then
        TargVal = Cell.Value
        Pattern = "0.00"
        If Abs(TargVal) > 999999 Then
          Pattern = Pattern & ",,""M"""
        ElseIf Abs(TargVal) > 999 Then
          Pattern = Pattern & ",""K"""
        End If
        Cell.NumberFormat = Pattern
      End If
    Next
  End If
End Sub
Since you didn't tell us what cells, I assumed Column "A" (change the Range inside the Intersect function to the cell range you want to have this functionality). After doing this, any numbers entered into those cells will adopt the number format you requested. Note that existing numbers will not change unless re-entered. You can do that one at a time or you can select all the existing numbers and execute this code from the Immediate Window...

Selection.Formula = Selection.Formula
 
Upvote 0
It works great, thank you.

However, another problem happens that when I uses this table to make a chart , the value displays on the chart is likely "text" such as: -1234567 not -1.2M.
 
Upvote 0
it works good for table and charts, thank you very much.
It works great, thank you.

However, another problem happens that when I uses this table to make a chart , the value displays on the chart is likely "text" such as: -1234567 not -1.2M.
You have written two "thank you" messages but failed to put the name of who you were replying to on either of them, so we have no idea which message is directed at which person.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top