Excel: Combine text and formulas in a cell

Whenever I analyze data in Excel, I format the spreadsheet to make it easier to read. A little attention to fonts, boxes and shading can help people understand the key data faster.

One way to give yourself some flexibility with formatting is to combine text and the results of a formula in a single cell. Just use the “&” operator to concatenate the text and the formula.

Consider this formula:

="Quantity: "&SUM(A1:A20)

Enter it into a cell, press enter and (assuming you have numeric values in cells A1 through A20) it will present this result in a single cell:

Quantity: 23

That kind of output’s pretty handy when you want to create a worksheet in your spreadsheet that aggregates data from other sheets while keeping the formatting simple.

51 responses to “Excel: Combine text and formulas in a cell”

  1. -L says:

    Now that I know concatentate is a real word, I will not bother playing editor. Other than that, I like the techie color and feel. It’s good all around.

  2. Janna says:

    But how do you add a format to the number returned? For example, if instead of 23 my total was 5342.16, how can I add a dollar sign and comma to make it show $5,342.16?

  3. Anthony says:


    Good question. Adding the dollar sign is pretty easy:

    ="Quantity: "&"$"&SUM(A1:A20)

    Not sure how to format the number, though, to add a comma.

  4. George says:

    How to I create :

    Date: 9/4/2013

    using text “date” and “=TODAY()”


  5. Anthony says:


    Try this:

    ="Date: "& TEXT(TODAY(), "mm/dd/yyyy")

    Credit to: http://stackoverflow.com/questions/6336518/concatenating-date-with-a-string-in-excel

  6. George says:

    That’s what I had already tried and it just gives me #NAME?
    I managed to find =CONCATENATE(“DATE: “, TEXT(TODAY(),”dd-mmm-yyyy”)) works.


  7. Matt says:


    You could use:

    ="Quantity: "&TEXT(A1,"$0,000.00")

    This will convert your number into text and format it to look the way you want. More about the TEXT function can be found in the help.

  8. Matt says:


    Sorry that formula should have read:

    ="Quantity: "&TEXT(SUM(A1:A20),"$0,000.00")
  9. Kim says:


    I’m trying to put a due date in a cell based on a date in another cell minus 28 days.

    I tried =”Due: “&(K234-28) where K234 is a date of 11/3/2013. I keep getting Due: 41547 as the result. It seems like it’s a formatting issue???

    Thank you!!!

  10. Anthony says:


    Some of the other comments in this thread hint at the answer:

    ="Due: "&TEXT((K234-28), "mm/dd/yyyy")
  11. Gavin says:

    I have a spread sheet with over 1000 client names. One cell has the full name and one cell will say male or female. Is it possible for a third column to combine this information and say Mr. John Doe? so something like this

    Mr. John Doe John Doe Male

    Because there is so many however, I’m trying to have the first cell populated via formula. If this is not possible how do I manually put Mr. and then a formula to pull in the full name?


  12. Anthony says:


    You can concatenate cells using the & character in a formula. You can also explore using the IF function to read the Male/Female cell and populate another cell with Mr. or Ms. based on the value.

  13. Johannes says:

    Hello Anthony!

    Thank you for a great post. How do I:
    *Choose numbers of decimals
    *Add percentage sign
    *Enters text also behind the formula.

    If the formula gives the answer 62,408543957.

    Text here 62,41% and text here.


  14. PAt says:


    Use the

    =ROUND(62,408543957; 2)


  15. paul says:

    Hi Anthony,

    is it possible to use excel to provide data within a sentence in an excel sheet?
    I have the following statement:
    The Promotors and existing Investors have invested £11,2 million (€13,1 million) in the project already and are seeking to raise an additional £16,0 million (€18,7 million).

    I am trying to link the monetary values to cells on another page of the worksheet so that as I change parameters, the investment values change. At the moment I have to amend the values manually. Thanks in anticipation.

  16. janil says:

    thank you so much.. 🙂

  17. Bobby says:


    I am trying to use a drop down list as input into a formula. For example, the drop down is in A1 of the current selected tab and it contains the names of tabs in my workbook. For now the drop down in A1 is set to Phase 0. What I want is for a formula to read the value of tab Phase 0’s cell A1. In cell A2 I have =’A1′!A1. Cell A2 throws a #ref error. If I type ‘Phase 0’!A1, the value I want from tab Phase 0 cell A1 comes up of course. What am I missing?

    Need your help please


  18. Bobby says:

    I just solved this with the indirect() function in excel…

  19. mh says:

    Alternative (IMHO easier/cleaner) way):

    Right-click/Format Cells/Number –> select ‘Custom Category’
    In the ‘Type:’ field, enter: “Quantity: “0
    Click ‘OK’
    Now in that cell, write ’23’.

    Cell will display: “Quantity: 23”
    Now copy/paste that format to the desired cells, or just select that format from the drop down menu next time.

  20. Roland says:

    Matt, thanks for solving Janna’s problem. That formula works perfectly.


  21. GREG says:


    I want to put in a cell like this:

    Avg Fabrication MH per ton = 100 MH x 7000 Ton.

    100 and 7000 are situated in different cells

    So I tried so far: =”AVG FABRICATION MANHOURS PER TON = “&ROUND(‘[E0418 SUMMARY.xlsx]Super-Structure’!$I$41,1)

    So it displays : AVG FABRICATION MANHOURS PER TON = 100
    but how to put the rest..
    Please help

  22. Bsteady says:

    Is it possible to join to lots of texts for example,
    =A2 & “Panels @” B2 & “m”?????

    Your help correcting this formula would be much appreciated.



  23. talha says:

    i want to add a number in a cell which contains a text. For Example, a cell contains “P/1” then how to get “P/2” in next cell and so on.

    Thanks in advance

  24. Jack says:

    Can u please rectified the said formula.

    “After TDS(10%) actual Amount received “&(ROUND(AP16,0),(AK16-(AK16*10)%))

    Jack V.

  25. ZJYB says:

    01/21/2015 at 3:30 pm
    i want to add a number in a cell which contains a text. For Example, a cell contains “P/1″ then how to get “P/2″ in next cell and so on.

    Thanks in advance


    I have had the same problem before, and used a relative reference to the row number to “add a number” to the next cell.

    If your first row has “P/1”, your next cell can contain the formula:


    This will produce “P/2” if the formula is place somewhere in the second row, “P/3” if placed somewhere in the third row, etc. This formula can then be filled down to add a number to each subsequent row.

    You can also add numbers within the formula to start at a desired number rather than the actual row number. For instance, =”P/”$row()+999 will result in “P/1000” if placed in the first row, “P/1001” if placed in the second row, etc. Again, this formula can then be filled down to add a number to each subsequent row.

    Hope this helps.

  26. Lauren says:

    Is there any way to have a formula extrapolate only the numerical value from a cell? I have a large spreadsheet with electrical numbers that include a watts (W) identification, is there some way to have a sum formula still tabulate the cells and simply ignore the letter?

  27. DJ says:

    Thanks! I’ve been fighting this for weeks

  28. Geet says:


  29. Jegs says:
    =UPPER('Your Final output with Formula'!A2)&", "&PROPER('Your Final output with Formula'!B2)&" "&LEFT('Your Final output with Formula'!C2,1))&".

    whats wrong about this formula ?

  30. hussam abdulalim says:

    i input:

    ="WEEKLY AVAILABILITY REPORT from  "&TEXT(H7, "[$-409]d mmmm;@")&" to "&TEXT(H13, "[$-409]d mmmm;@")

    to get this text:
    WEEKLY AVAILABILITY REPORT from 15 July to 21 July

    provided that H7= 15/7/2015 and H13= 21/7/2015

    H13 value is provided by another formula and H7 is the variable that i input manually

  31. Janet says:

    I have the following in a merged and centered cell (like a heading). Can I create a link to a separate tab in the workbook to provide the date (ie, August 31, 2015, in this case)? I have many places where dates need to be updated monthly and quarterly and it is manual and time consuming.

    Combined Consolidated Rolling 12 Month Statement of Operations
    For the Twelve Months Ending August 31, 2015 – LENDER VERSION

  32. Anthony says:


    Sure. You could set up a worksheet (tab) in your spreadsheet. Call it date or something similar, and enter the date in one of the cells. Then you could refer to that worksheet and cell from all the other locations in the file that need that date. Update the date once and it spreads across the whole thing. Handy!

    The way to access another sheet is referencing it by name. e.g.:


    To combine dates into text requires a little extra formatting:

    ="The date is "&TEXT(date!A1,"mm/dd/yyyy")&" for this report."
  33. Janet says:

    Yes, I have those references working. I’m having trouble with the titles that are currently in a cell with multiple lines wrapped and centered. I was wondering if I had to break into multiple rows to get a formula to work. I am using a separate “dates” tab within the workbook. Thanks for your help!

  34. Ted says:

    LAUREN……Use the FIND & REPLACE function [Cntrl+H] to get rid of the “W”in your spreadsheet then you can use SUM function to sum the values

  35. Ted says:

    LAUREN……To be more explicit, select the range of cells you want to remove the “W” from the cell. Press Cntrl+H to bring up the FIND&REPLACE function. Put “W” (no quotes) in the FIND field and leave the REPLACE field blank. This will remove the “W” from all fields you have select

  36. Lisa says:

    how to do solve this?
    Data what I want it to be
    excel #2003 myexcel#2003
    #add in “my” then replace ” #” to “#”
    pls help thanks

  37. Dewy says:

    Can anyone tell me how to use a number in a cell as part of a formula. I have two cells that different numbers will be input to. One number references the sheet, the other a cell number in that sheet. example: cell B5 has the number 1 in it. cell C5 has 101 in it. I tried =’C5′!&A’B5′ since I want to reference cell A1 in sheet 101, but I know I’m way off track. Any help?

  38. Dewy says:

    This generated the correct cell but doesn’t actually reference it.

  39. TED says:

    Dewey, you were on the right track……now, in another cell use the INDIRECT formula to convert the text value to a recognizable cell reference. If the output of your first formula was in cell D5, then in another cell, let’s say E5, write =INDIRECT(D5)

  40. Dewey says:

    Thanks! The INDIRECT function worked perfect.

  41. KB says:

    How would I state:

    There are 20 widgets in the box.

    Cell B5 has the quantity of widgets.

    Something like:
    =”There are “&=($B$5)”widgets in the box.”

  42. Anthony says:


    You’re close. Try:

    ="There are "&B5&" widgets in the box."
  43. CL says:

    Hi Anthony.

    I am trying to make a ratio with actual raw data as a fraction of 2 separate cells without the fraction being reduced.

    Example: Cell G7 contains a formula yielding the value 8. And cell F7 contains a formula yielding the value 16. In H7, I want to put these 2 data points in a fraction (or a ratio would also work) of 8/16 without reducing that fraction. Any way to do this?

  44. Anthony says:



  45. CL says:

    credit to:
    Bernie Deitrick, Excel MVP 2000-2010

    You have to use helper cells.

    Enter the number correct in, say, cell E2. In F2, enter the number of questions, and in G2, enter the formula =E2/F2. In H2, enter the formula = E2 & “/” & F2

    Use G2 for calculations, and H2 for display.



  46. CL says:

    just saw your reply too. thanks anthony!

  47. Sheri says:

    Is there a clean way to have the choice in the same cell to either use % or $ and have it formulate in a later cell? Could a drop down be used? Also must function within online OneDrive.

  48. Johnny says:

    Trying to sum a range with text and numbers. Cells contain 8,W. How can add just the numeric part of the cell?


  49. Anthony says:


    Suggest you use the “Text to Columns” command to separate the cell contents into two cells — one for the number and the other for text.

  50. Lloyd says:

    Hi all,

    I would like to create a label or text name for a cell that has a value i.e.
    1250 = ItalianCotton
    2500 = ItalianCanvas
    355 = ItalianLinen

    So that when I select from my drop-down list, the name displays that the cell is assigned however is able to allow calculation of the value of the cell for my formula:


Leave a Reply

Your email address will not be published. Required fields are marked *