Why is this OK: =TEXT(A8/1000,"#.00 K")
But this returns #VALUE!: =TEXT(A8/1000000,"#.00 M")
(I know I can use =TEXT(A8/1000000,"#.00")&" M" )
It seems some letters are allowed as part of the format in TEXT while others
are not.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Some symbols are predefined: D, DD, DDD, M, MM, MMM, H, HH, M, MM, S, SS....

Maybe just telling excel that you want the character M is best:

=TEXT(A8/1000000,"#.00 \M")



Bernard Liengme wrote:

Why is this OK: =TEXT(A8/1000,"#.00 K")
But this returns #VALUE!: =TEXT(A8/1000000,"#.00 M")
(I know I can use =TEXT(A8/1000000,"#.00")&" M" )
It seems some letters are allowed as part of the format in TEXT while others
are not.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

--

Dave Peterson

Hi Bernard,

I am guessing it's because M is either part of a date (month) or part of
time (minute), anything that is refused as a custom format using
formatcellsnumbercustom will return a value error using the text
function, instead you can use

=TEXT(A8/1000000,"#.00 ""M""")




--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Bernard Liengme" wrote in message
...
Why is this OK: =TEXT(A8/1000,"#.00 K")
But this returns #VALUE!: =TEXT(A8/1000000,"#.00 M")
(I know I can use =TEXT(A8/1000000,"#.00")&" M" )
It seems some letters are allowed as part of the format in TEXT while
others are not.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Many thanks to Dave and Peo
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
Why is this OK: =TEXT(A8/1000,"#.00 K")
But this returns #VALUE!: =TEXT(A8/1000000,"#.00 M")
(I know I can use =TEXT(A8/1000000,"#.00")&" M" )
It seems some letters are allowed as part of the format in TEXT while
others are not.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email