Using TEXT forumla with CONCATENATE to insert Commas into numbers

[chefelf]

I'm trying to use the TEXT formula to insert commas into large numbers.

For example:

Cell S2 = 6000000000 (displays as 6,000,000,000 with formatting)

if Cell U2 = CONCATENATE(S2) does not show commas.
if Cell U2 = CONCATENATE(S2,TEXT(S85,"000,000,000")) number displays properly.

if S2 = 35,000, formula does not display properly: (000,035,000)

Does anyone know of a formula to make the number formatting appear correctly for all numbers, large and small?

Thanks in advance for any help! Hopefully I explained that clearly.

 

[dcardno]

Try:
...text(S85,"#,##0")

 

[Richard Schollar]

Hi

Use

TEXT(S2,"#,##0")

no need to repeat the , and 0/#s.

 

[chefelf]

Thank you SO much. That was exactly what I was looking for! Works perfectly!

 

[barry houdini]

You could also use the FIXED function, i.e.

=FIXED(S2,0)