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.

31 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)

    function

  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.

  20. Roland says:

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

    Cheers.

  21. GREG says:

    dEAR ANTHONY..

    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.

    Sincerely

    Brad

  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)%))

    Regards,
    Jack V.

  25. ZJYB says:

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

    ="P/"&row()

    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:

    Thanks.

  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

Leave a Reply