Text Function 2nd argument

  • Thread starter Thread starter Gene
  • Start date Start date
G

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:)
 
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
 
=TEXT(A1,"""$""0.0,,""M""")
BE CAREFUL of the number and location of the double quotes!!
 
A little bit more compact (and no worries about double quotes)...

=TEXT(A1,"\$0.0,,\M")
 
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 said:
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 said:
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:)
Click to expand...
Click to expand...
 
Actually, the back slash in front of the $ sign isn't needed...

=TEXT(A1,"$0.0,,\M")
 
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 said:
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 said:
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:)
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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:)
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top