Number Formatting using a Formula?

[Karthik-Excelsior]

Hi Excel experts,

Apart from cell formatting, is there a way to format a number using a formula?

For eg. I have a sentence which runs like this:

"The sales on these items have yielded $65.7M in cash"

It's actually a concatenation of text and formula like this:

="The sales on these items have yielded $"&SUM(B:B)&" in cash"

But, when the formula sums up the sales fig, it shows like this:

"The sales on these items have yielded $65684234 in cash"

Is there a way we can format the number in this sentence to be represented as "$65.7M"?

Any help here is much appreciated!

Thanks,

Karthik

 

[pgc01]

Hi Karthik

Try:

="The sales on these items have yielded "&TEXT(SUM(B:B),"$0.0,,\M")&" in cash"

 

[Karthik-Excelsior]

Excellent.. thx PGC! That worked... B.R.I.L.L.I.A.N.T.

 

[pgc01]

You're welcome. Thanks for the feedback.