Rounding Of Concatenated Costs Field

[Caldo]

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

 

[T. Valko]

You should've posted your formula.

Try this:

A1 = $29,501,341.16

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

 

[Teethless mama]

=DOLLAR(A1/(10^6),1)&"M"

 

[JoeU2004]

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

I have no idea what you are concatenated and why, since you neglected to
post your formula.

If you are merely concatenating a number and "M" (e.g. =A1/1000000&"M"), you
can dispense with dividing by 1000000 and concatenating "M", and instead use
the following custom format "$0.0,,\M" without the quotes.

But if you still need concatenation for some other purpose (e.g. =A1&"
total"), you can apply the same idea using the TEXT function, for example:
=text(A1,"$0.0,,\M")&" total".

 

[Shane Devenshire]

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""")

 

[Shane Devenshire]

Hi,

I never get to use that function - nice.

You can shorten it to

=DOLLAR(D6/10^6,1)&"M"

because order of presidence is ^ before * or /.

 

[Rick Rothstein]

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

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

 

[Caldo]

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

 

[T. Valko]

Administration ($27.58053056)

How can I get it to round to $29.6M

You can get it rounded to $27.6M...

Using Ricks method:

=G6&" "&TEXT(J6,"($0.0,,\M)")

 

[Rick Rothstein]

You don't need the CONCATENATE function to put text together, you can link
them with an & character. Also, since we are using the TEXT function, we can
put your space and parentheses right inside the TEXT function's pattern
string. This should do what you want...

=G6&TEXT(J6," ($0.0,,\M)")

 

[Caldo]

That got it. Thanks for the help

You don't need the CONCATENATE function to put text together, you can link
them with an & character. Also, since we are using the TEXT function, we can
put your space and parentheses right inside the TEXT function's pattern
string. This should do what you want...

=G6&TEXT(J6," ($0.0,,\M)")