Formating cell (text + number + text)

[pruleone]

Hello (again ),

I have used this kind of formula =ROUND(H11;0)&" "&Sheet2!V7

Where H11 is number and Sheet2!v7 is some text line. (Also used so, that text is before number or even so that text is before and after number).

This calculation will show me number and text in one cell, it is good.

If I have number for example 12453, then usually excel will use thousand separation and show me number as 12 453 - if there is only number in cell.
But when I use formula like I described above then excel would not use this thousand separation, even when I choose from format cell as number.

Maybe someone knows what I must to, that excel should use thousand seperator even when number is between text?

Or is it even possible?

And I use excel 2003.

 

[Loomah]

try this
=text(ROUND(H11;0),"#,###")&" "&Sheet2!V7

where "#,###" is the representation of the thousands indicator. i guess you'll have to adjust this to agree with your regional settings.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[pruleone]

Thank you,

I placed it as "# ###" and it worked.

 

[combo]

You convert number to text, so the number inside cannot be formatted.
You can instead:
1. use TEXT function to convert number to text and add the rest of text:
[tt]=TEXT(A2,"# ##0")&A1[/tt],
2. keep number formatted with number format, no real rounding and dynamic text in this case. Custom format:
[tt]# ##0" some text"[/tt]

combo

 

[pruleone]

This solution what combo described is better one.

As I use in some places decimals also, then I wrote i like =TEXT(A2;"# ###,00")
Then if I have number with decimal places, like 1001,11 then it will be showed as 1 001,11. If I have number like 1000, then it will be showed like 1 000,00.

I also tried to use formula like =TEXT(A2;"# ###,##"), but then if I number dosen't have decimals, then it will be showed like 1 000,