Text Function 2nd argument

[Gene]

I would like to format 1000000 (1 million) using the Text Function into $1M.
It works for K:
a1=1000
=TEXT(D15,"$#,###,k") Works fine
but
when a1=1000000
and I replace the K with an M, I get a #value error
I would like my end result to look like: $1M

It was successful just using the Format>cells>custom.
THANKS!
Gene

 

[Mike H]

Hi,

Here's a number format that does what you want
[>=1000000]0,,"M" ;General

but its better I think) if you adapt this to include thousands with this

[>=1000000]0,,"M";[>=1000]0,"K";0

Mike

 

[Gary''s Student]

=TEXT(A1,"""$""0.0,,""M""")
BE CAREFUL of the number and location of the double quotes!!

 

[Gene]

THANKS for the QUICK reply!

 

[Rick Rothstein]

A little bit more compact (and no worries about double quotes)...

=TEXT(A1,"\$0.0,,\M")

 

[Rick Rothstein]

If the OP really wants to use the TEXT function as he stated, your
suggestion can be applied to it (with a minor modification)...

=TEXT(A1,"[>=1000000]$0.0,,\M;[>=1000]$0.0,\K;0")

Note that I added the $ sign the OP showed in his example and I provided for
a single decimal place as Gary''s Student suggested in his response.

--
Rick (MVP - Excel)

Hi,

Here's a number format that does what you want
[>=1000000]0,,"M" ;General

but its better I think) if you adapt this to include thousands with this

[>=1000000]0,,"M";[>=1000]0,"K";0

Mike

I would like to format 1000000 (1 million) using the Text Function into
$1M.
It works for K:
a1=1000
=TEXT(D15,"$#,###,k") Works fine
but
when a1=1000000
and I replace the K with an M, I get a #value error
I would like my end result to look like: $1M

It was successful just using the Format>cells>custom.
THANKS!
Gene

 

[Rick Rothstein]

Actually, the back slash in front of the $ sign isn't needed...

=TEXT(A1,"$0.0,,\M")

 

[Mike H]

missed the dollar bit

[>=1000000]"$"0,,"M";[>=1000]"$"0,"K";0

Just a point, you may have a reason for wanting to use text but if you do
it's no longer a number. Using a format doesn't change the underlying value,
only what you see.

Mike

Hi,

Here's a number format that does what you want
[>=1000000]0,,"M" ;General

but its better I think) if you adapt this to include thousands with this

[>=1000000]0,,"M";[>=1000]0,"K";0

Mike

I would like to format 1000000 (1 million) using the Text Function into $1M.
It works for K:
a1=1000
=TEXT(D15,"$#,###,k") Works fine
but
when a1=1000000
and I replace the K with an M, I get a #value error
I would like my end result to look like: $1M

It was successful just using the Format>cells>custom.
THANKS!
Gene

 

[Mike H]

Rick,

Thanks Rick, I was aware of that. I was trying to guide the OP away from
text to preserve the value of the number.

Mike

If the OP really wants to use the TEXT function as he stated, your
suggestion can be applied to it (with a minor modification)...

=TEXT(A1,"[>=1000000]$0.0,,\M;[>=1000]$0.0,\K;0")

Note that I added the $ sign the OP showed in his example and I provided for
a single decimal place as Gary''s Student suggested in his response.

--
Rick (MVP - Excel)

Hi,

Here's a number format that does what you want
[>=1000000]0,,"M" ;General

but its better I think) if you adapt this to include thousands with this

[>=1000000]0,,"M";[>=1000]0,"K";0

Mike

I would like to format 1000000 (1 million) using the Text Function into
$1M.
It works for K:
a1=1000
=TEXT(D15,"$#,###,k") Works fine
but
when a1=1000000
and I replace the K with an M, I get a #value error
I would like my end result to look like: $1M

It was successful just using the Format>cells>custom.
THANKS!
Gene

 

[Gary''s Student]

Very nice!
--
Gary''s Student - gsnu200812

Actually, the back slash in front of the $ sign isn't needed...

=TEXT(A1,"$0.0,,\M")

 

[ShaneDevenshire]

Hi,

Just in case it's not clear, everyone of the suggestions your recieved can
be added as cell formats, so you don't need to use the TEXT function to
format a cell. Choose Format, Cells, Number tab, Custom and enter the codes
you recieved on the Type line without some of the quotes "0.00" should be
0.00 for example.