Results 1 to 5 of 5

Using TEXT forumla with CONCATENATE to insert Commas into numbers

This is a discussion on Using TEXT forumla with CONCATENATE to insert Commas into numbers within the Excel Questions forums, part of the Question Forums category; I'm trying to use the TEXT formula to insert commas into large numbers. For example: Cell S2 = 6000000000 (displays ...

  1. #1
    New Member
    Join Date
    May 2008
    Posts
    2

    Default Using TEXT forumla with CONCATENATE to insert Commas into numbers

    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.

  2. #2
    Board Regular
    Join Date
    Nov 2002
    Location
    Vancouver, Canada
    Posts
    450

    Default Re: Using TEXT forumla with CONCATENATE to insert Commas into numbers

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

    Dean

  3. #3
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Using TEXT forumla with CONCATENATE to insert Commas into numbers

    Hi

    Use

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

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

    Using xl2013

  4. #4
    New Member
    Join Date
    May 2008
    Posts
    2

    Default Re: Using TEXT forumla with CONCATENATE to insert Commas into numbers

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

  5. #5
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,957

    Default Re: Using TEXT forumla with CONCATENATE to insert Commas into numbers

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

    =FIXED(S2,0)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com