Mixing Number and Text Formats

[jlawless]

Morning all,

I have a cell that concatenates several others, the complete cell reads
something like this:

BUY 200000 LMT 23.67

The text will change so I can't hard code that, but what I would like
is a comma separator (200,000) for the 200000 (this number will also
change).

After spending far too much time on this, I have a feeling that you
can't mix a text and number custom format.

Prove me wrong.

Cheers,

J.

 

[Paul]

Morning all,

I have a cell that concatenates several others, the complete cell reads
something like this:

BUY 200000 LMT 23.67

The text will change so I can't hard code that, but what I would like
is a comma separator (200,000) for the 200000 (this number will also
change).

After spending far too much time on this, I have a feeling that you
can't mix a text and number custom format.

Prove me wrong.

As an example, suppose you have the text "BUY" in A1 and the number 200000
in B1.
=A1&" "&TEXT(B1,"#,##0")
will give the result "BUY 200,000"

In other words, you can specify the format you want the 'number' to have by
using the TEXT function.

 

[Andy B]

jlawless

Depending on the number of digits in your number, you could use:
LEFT(D7,LEN(D7)-3)&","&RIGHT(D7,3) to recreate the thousand separator.
If the number of digits varies, the formula would have to be rewritten
another way.

Andy

 

[jlawless]

Paul, Perfect! Just what I wanted cheers.

Andy, The number would change so recreating the thousand separator
would be a little more complex, but thanks anyway.