Here is the current formula:

=CONCATENATE(G6, " ","(", "$",J6/(10^6),")")

which returns this

Administration ($27.58053056)

How can I get it to round to $29.6M

"Rick Rothstein" wrote:

Or you can use the same pattern in the TEXT function that you proposed for
the Custom Format...

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

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

I don't follow this concatenation thing but if the number in the cell is a
number then create the following custom format:

$0.0,,\M

To do this choose Format, Cells, Number tab, Custom, and enter the above
on
the Type line.

If you want to use a formula approach then

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Caldo" wrote:

The second field in my concatenate function is actual costs, the first is
line of business. One value, for example, is $29,501,341.16 which I can
round
down to $29,501,341 but the concatenated value is fully exploded with the
decimals. What I ideally would like to do is have the concatenated value
read
$29.5M. Any ideas? Excel 2007