Currency Format in Mixed Text Fields

Topic

New Reply

Somehow I’ve forgotten how to do this.

I have a cell with a number in Currency format.  The cell also contains stuff in Text format. The currency format doesn’t appear when the two are combined. (The commas go away.)

What do I do to preserve the currency format on that part of the cell?

Lou Sander

Reply | Quote

Replies

Once you add any text it is no longer a number (currency is just a number with 2 decimal places) and the format is not applied.

Try using the TEXT command and specifying a format, like this:
=TEXT(A1,"$#,##.#0")&" extra text"
or
=TEXT(123.45,"$#,##.#0")&" extra text"

cheers, Paul

1 user thanked author for this post.

ussrankin

Reply | Quote

Simple, but I had forgotten it, and couldn’t remember the Help topic. At 82, this is happening more often.

Lou Sander

Reply | Quote

Of course, you can’t then use the number from the cell unless you use SPLIT to break it up again.
The best method is to put the amount into a cell on its own and then use the first option to show the value and text in another cell.

cheers, Paul

1 user thanked author for this post.

ussrankin

Reply | Quote