TEXT function question

[Bernard Liengme]

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.

 

[Dave Peterson]

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")

 

[Peo Sjoblom]

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
format>cells>number>custom 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]

Many thanks to Dave and Peo