Solved I want to get excelt to show the values in cells in millions

October 25, 2012 at 08:31:43
Specs: Windows 7
how do i get excel to show 325,987,654 as 325million

See More: I want to get excelt to show the values in cells in millions

Report •


#1
October 25, 2012 at 13:06:56
✔ Best Answer
You can do it with a formula, but it is not a very good option.
I would advise using a Macro.

Here is a formula that should give you what your looking for:

This formula expects your number to be a regular dollar amount
WITH decimal places, IE 1001.12
and using zero zero, IE 1001.00 does not work
because Excel will strip off the trailing zeros,
so you end up with 1001

=IF(LEN(A1)>=10,LEFT(A1,LEN(A1)-9)&"Million",IF(OR(LEN(A1)=9,LEN(A1)=8,LEN(A1)=7),LEFT(A1,LEN(A1)-6)&"Thousand",A1))

If your numbers NEVER have decimal places, you can use this formula:

=IF(LEN(A1)>=7,LEFT(A1,LEN(A1)-6)&"Million",IF(or(LEN(A1)=6,len(a1)=5,len(a1)=4),LEFT(A1,LEN(A1)-3)&"Thousand",))

But I would really advise getting a Macro, there are several out on the web.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question