=CONCATENATE(50*10^9*121, "yen")

[Guest]

The above formula, a conversion of US dollars to Japanese yen, is returned as
"6050000000000 yen" (6.05 trillion yen) as opposed to 6,050,000,000,000 yen

Is there a way to force the commas to appear? Right now, as the formula
stands, CONCATENATE treats 50*10^9*121 as text, not a number. (121 is the
amount of USD per 1 JPY).

(Yes this question could be avoided by formatting the calculation as
currency and giving it the yen symbol. But I've been asked to do it this
way. Odd, I know.)

Thanks for any ideas.

Dave

 

[David Biddulph]

=CONCATENATE(TEXT(50*10^9*121,"#,#,#,#"), " yen")

 

[Guest]

Use the text function to specify the formatting:
=TEXT(50 * 10 ^ 9 * 121,"#,###") & " yen"

 

[Guest]

Great, thanks.

 

[Guest]

Thanks.