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.

19 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:

    Janna,

    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()”

    Thanks!

  5. Anthony says:

    George,

    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.

    Thanks.

  7. Matt says:

    Janna,

    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:

    Janna,

    Sorry that formula should have read:

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

    Anthony,

    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:

    Kim,

    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?

    thanks!

  12. Anthony says:

    Gavin,

    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.

    FOR EXAMPLE:
    If the formula gives the answer 62,408543957.

    HOW DO I GET IT TO LOOK LIKE THIS:
    Text here 62,41% and text here.

    Johannes.

  14. PAt says:

    Johannes

    Use The =ROUND(62,408543957; 2) fuction

  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.
    Paul.

  16. janil says:

    thank you so much.. :)

  17. Bobby says:

    Hello,

    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

    Bobby

  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.

Leave a Reply