Can I show a number like 1,935,674,923 as 1.9B?

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I would like to reformat 10- and 11-digit the numbers (e.g. like
1,935,674,923 as 1.9B). Can this be done?
 
Dave,

Use a custom format of

#.#,,,"B"

or

0.0,,,"B"

depending on how you want to show smaller numbers.


HTH,
Bernie
MS Excel MVP
 
I would like to reformat 10- and 11-digit the numbers (e.g. like
1,935,674,923 as 1.9B). Can this be done?
Click to expand...

Try the following Custom Number format:

0.0 "B"

Note: There are 3 spaces before "B".

Hope this helps / Lars-Åke
 
This does not seem to work with US version of Excel. You just get the large
number with B added to the end. We need to use commas not spaces: 0.0,,,"B"
best wishes
 
This does not seem to work with US version of Excel. You just get the large
number with B added to the end. We need to use commas not spaces: 0.0,,,"B"
best wishes
Click to expand...

Thanks for pointing this out.
I didn't notice that the OP had these "," in the number.

Don't think it has to do with the version of Excel though.
It is the "Digit Grouping Symbol" in the "Regional and Language
Options" settings of the computer OS that controls this.

So a general answer could be:
Use a custom number format like
0.0xxx"B" (where x is the Digit Grouping Symbol).

Lars-Åke
 
I agree. I was being a bit vague when I said "North American version of
Excel". I should have send "those using commas to separate groups" I like
to use spaces in my own work but it's a hard sell this side of the Atlantic
Cheers
Bernard
 
Back
Top