Mixing Number and Text Formats

J

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.
 
P

Paul

jlawless said:
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.
Click to expand...

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.
 
A

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
 
J

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.
 
Top