Forum Discussion

paul_power's avatar
paul_power
Copper Contributor
Aug 22, 2018

Excel - CONCATENATE

I am trying to include a number in text but when I use the CONCATENATE function I lose the comma - eg 123,456 converts to 123456. Is there a way I can retain the comma?

excel
Formulas and Functions
  • Colin Foster's avatar
    Colin Foster
    Copper Contributor
    Aug 22, 2018
    Yes, if Number is in cell D21 & the text you want is in E21 use =TEXT(D21,"#,###")&E21 & that will format your number to the custom format of #,###. Basically, the format after the comma in the TEXT function is how you want your "number" to look (& matches the custom number formats available). Just have to remember to encase in a pair of " " marks
    • paul_power's avatar
      paul_power
      Copper Contributor
      Aug 22, 2018

      Thank you Colin

       

      My problem is that I am trying to insert a numbers into a body of text and have been unable to apply your solution to this. For instance in "The company's sales were £300,000, purchases £160,000 and gross profit £140,000." what formula should I use? Assume that the figures are in cells A1, A2 and A3 respectively.

       

      Many thanks

      • vijaykumar shetye's avatar
        vijaykumar shetye
        Brass Contributor
        Aug 22, 2018

        Hi Paul_Power,

        the Formula remains the same as given by Colin Foster. Use it as shown below.

        ="The company's sales were £"&TEXT(A1,"#,##0")&", purchases £"&TEXT(A2,"#,##0")&" and gross profit £"&TEXT(A3,"#,##0"&".")

         

        Regards,

         

        Vijaykumar Shetye,

        Panaji, Goa, India

Resources