Power Query Course in Spanish
Results 1 to 4 of 4

Thread: adding K or M to number

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    635
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default adding K or M to number

    Hi
    I want to add K or M to numbers, for example 1000 i want it this way 1K and 1000000 i want it 1M.
    So for 1K I did this custom formatting

    #; \K

    It did work find in the number is 1000 and above but if the number is less than 1000 then excel will put K. Is it possible to remove K when the number is less than 1000?

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,671
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: adding K or M to number

    Maybe something like this in custom formatting.
    Code:
     [>=1000000]#.0,"M";[>=1000]#.0,"k";#

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,038
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    13 Thread(s)

    Default Re: adding K or M to number

    Quote Originally Posted by AhoyNC View Post
    Maybe something like this in custom formatting.
    Code:
     [>=1000000]#.0,"M";[>=1000]#.0,"k";#
    There should be two commas after the 0 for the "M" format, not just one as you posted above...

    [>=1000000]#.0,,"M";[>=1000]#.0,"k";#
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular
    Join Date
    Mar 2016
    Posts
    635
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: adding K or M to number

    Thank you all for the help. Now I understand the code but I now have a question about these 3 parts. I know this synatax

    positive ; negative ; zero ; text

    why excel use your code for +ve numbers only, i thought your second part [>=1000]#.0,"k"
    should be used for -ve numbers?


User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com