| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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:) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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:) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function argument not returning a value | Excel Worksheet Functions | |||
| How can I get around 30 argument limit of SUM function? | Excel Worksheet Functions | |||
| Need Function Argument | Excel Worksheet Functions | |||
| text as an argument of SUM function | Excel Worksheet Functions | |||
| How do I specify more than one argument for the SUMIF function? | Excel Worksheet Functions | |||
Linear Mode
