Number formatting from thousand to millions
Hi all,
Excel displays my number in thousand format which is like this 11,90,250.00
I want the same number to be displayed in million format which is like 1,190,250.00 without changing the system settings as this report is used on different machines with different system settings.
Please let me know how can I do this number formatting?
Thanks in advance.
Your first format makes no sense. I have never seen numbers formatted like that.
The format you want is simply the "comma" format. I no longer have a version of Excel that is 17 years old but if you select the cells, then go to Cell, Format, and format as "Number" with two decimal places, it will look like your example. I am not sure of the exact menu sequence but you want to get here:
numberformat.JPG
I agree with 6string, there is no (standard) number format like that - perhaps you had a typo and left out a digit?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
6StringJazzer,
This format is called "Thousand" which is commonly used in India. Excel fetches the number format from "Region" Settings in "Control Panel".
If I change the region settings in control panel then the number format changes to "Millions" but I can not change the region settings for all the users who are opening this report and hence I was looking for something which is inside Excel rather then system settings.
I don't understand why you need to do anything at all - if the other machines don't use the same regional settings, then the number will show in the standard millions format automatically, surely?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Ali,
So that my excel shows the numbers in millions format regardless of any settings that machine has. Even if the machine is having the setting for Thousand this excel file should display the number in millions only.
Thanks,
May be:
In Custome / type:
#","000","000
or
#","###
Quang PT
Update:
[ < 1000000]#","###.00 ;#","###","###.00
to add more conditions for number string less than 7 digits ( < 1,000,000) and more than 7 digits ( > = 1,000,000)
Thanks Babo, I tried your first custom format #","000","000
the result was like this:
image_2020-11-20_153013.png
it is adding leading comma if number is small.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks