Search code examples
excelgoogle-sheetsexcel-formulalibreoffice-calc

Excel cell with line breaks. tabs with date values

I didn't found that whats wrong in below code

=” Please find a report for  “&TEXT(B4, "dd mmmm, yyyy ")&" | "&TEXT(B4, "dddd");

I also want the below lines in single cell.

Please find a report for 30 August, 2018 | Thursday
    Total B.T.    =  03 : 10
    Total N.B.T.  =  03 : 20
Solution

  • Some of the quotes are "fancy quotes" that are different for opening and closing quotes. It is likely that you copied the formula from a web site or from a Word or PowerPoint document, which have a nasty habit of converting straight quotes into "fancy" opening and closing quotes.

    This formula works fine in my tests:

    ="Please find a report for  "&TEXT(B4, "dd mmmm, yyyy ")&" | "&TEXT(B4, "dddd")

    enter image description here

    Edit: You can insert a line break with char(10) and concatenate more text and formulas that look up values, for example:

    ="Please find a report for  "&TEXT(B4, "dd mmmm, yyyy ")&" | "&TEXT(B4, "dddd")
&CHAR(10)&"some more stuff"
&CHAR(10)&"color: "&VLOOKUP(A1,$D$4:$E$10,2,FALSE)

    Excel cannot use a Tab character in a cell. You will need to add spaces for that.

    I used Alt+Enter in the formula to break the formula up into several rows and make it easier to read.

    enter image description here