Text Function 2nd argument
 

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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
"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

=TEXT(A1,"""$""0.0,,""M""")
BE CAREFUL of the number and location of the double quotes!!
--
Gary''s Student - gsnu200812


"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

THANKS for the QUICK reply!

"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
=TEXT(A1,"""$""0.0,,""M""")
BE CAREFUL of the number and location of the double quotes!!
--
Gary''s Student - gsnu200812


"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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)


"Mike H" wrote in message
...
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
"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
A little bit more compact (and no worries about double quotes)...

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
=TEXT(A1,"""$""0.0,,""M""")
BE CAREFUL of the number and location of the double quotes!!
--
Gary''s Student - gsnu200812


"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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

"Mike H" wrote:

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
"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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

"Rick Rothstein" wrote:

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)


"Mike H" wrote in message
...
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
"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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


"Rick Rothstein" wrote:

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
A little bit more compact (and no worries about double quotes)...

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in
message ...
=TEXT(A1,"""$""0.0,,""M""")
BE CAREFUL of the number and location of the double quotes!!
--
Gary''s Student - gsnu200812


"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)

Text Function 2nd argument
 

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.

--
Thanks,
Shane Devenshire


"Gene" wrote:

THANKS for the QUICK reply!

"Gene" wrote:

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 Formatcellscustom.
THANKS!
Gene:)