Mixing Number and Text Formats

  • Thread starter Thread starter jlawless
  • Start date Start date
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.
 
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.
 
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
 
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.
 
Back
Top