Google Sheets- Can I add text to a cell that already has a formula?

Question

This is very much a beginner question.

On Google Sheets is there any way to add text to a cell with a formula?

Example:

Essentially I would like to have the text and the date for the Friday cell in one cell. However, I would like to preserve the formula as well and am not sure how to go about it.

I tried finding a solution on the internet but the only results that came up consisted of "splitting a cell into columns” which is not helping(perhaps I am implemented this wrong).

Answer 1

You can concatenate a text string with a formula using & like this:

=formula&" text string"

You may need to wrap your formula in the TEXT function with the appropriate arguments in order to maintain the correct format, as the concatenated text may invalidate any number/date formatting previously chosen.

Answer 2

Spoiler

If you prefer not to read all the way through, the final formula is here:

=BYROW(dataSheet!A:Z, LAMBDA(r,
  IFERROR(TEXTJOIN(CHAR(10),TRUE, 
    TEXT(DATEVALUE(INDEX(r,,1)), "dddd mm/dd"), 
    FILTER(r,COLUMN(r)<>1)))
))

Data vs. Presentation

The way to achieve your goal natively in Sheets is to separate the actual data from the presentation of the data. This allows you to manipulate the data using formulas without affecting the data itself.

Example

I created two sheets one for your data dataSheet, and another for your presentation presentationSheet.

The reason two sheets were used (instead of one) was to allow the height of the rows and the width of the columns to be maintained separately since you want to have multiple lines in a single cell. It was a personal choice, and one sheet could also have been used.

dataSheet Notes

1. Dates are entered in a column. The decision of a column vs a row was an arbitrary design choice based on the expected number of dates vs text strings;
2. Text strings were placed in cells in any column as long as they were located in the same row as the date to which they applied;
3. Manipulating date formating in dataSheet is unnecessary (optional) as the format will be set in the formula itself;

presentationSheet Notes

This is where the formula is placed to manipulate and present the raw data.

1. The formula returns all dates from dataSheet but the formula could be adapted to return a subset of the data based on date range and other criteria;
2. The formula creates a single multi-line text string from every date, and any values it finds in the same row as that date;
3. The string in #2 is built using TEXTJOIN. Blank values are ignored, and any values found are delimited with CHAR(10) which represents the line break character;

Basic Formula

Basic Formula (Step 1 of 2)

The basic formula works for one row of data from dataSheet. The completed basic formula will be adapted into the final formula to apply to all rows (dates).

# Basic Formula (Step 1 of 2)

=TEXTJOIN(CHAR(10),TRUE, 
   TEXT(dataSheet!A1, "dddd mm/dd"), dataSheet!B1:Z1)

<sub>Basic Formula (Step 1 of 2)

Source Data</sub>

Basic Formula (Step 2 of 2)

Step 2 improves the basic formula to address NULL values as well as other non-date values (strings, non-date numbers, etc.) in the dates column. NULL values, in particular, will be expected in our dates column in unpopulated rows.

1. Sheets will coerce non-dates to dates in certain cases such as our use case with the TEXT function. For example, =TEXT(A1, "mm/dd/yyyy hh:mm:ss") will return 12/30/1899 00:00:00 where A1 is NULL (blank) or 0.
   
2. This behavior can be avoided by first wrapping the value in the DATEVALUE function. DATEVALUE will not coerce NULL and 0 values to a date, instead returning a #VALUE! error.
   
3. Finally wrapping the resulting formula in the IFFERROR function enables it to return a NULL (blank) value instead in place of any error.
   

# Basic Formula (Step 2 of 2)

=IFERROR(TEXTJOIN(CHAR(10),TRUE, 
    TEXT(DATEVALUE(dataSheet!A1), "dddd mm/dd"), dataSheet!B1:Z1))

Formula for All Rows

You could copy and paste the basic formula down the column and be done, however, the formula could break if you make changes to your dataSheet such as adding and removing data. You might get errors or return the wrong results (without errors) for one or both of the dates and the text strings. The formula below automatically adjusts for any number of rows of data and will not break if adding, removing, and rearranging source data.

The BYROW function enables using a LAMBDA function to apply the same formula row by row to a range. The basic formula is that formula we want to apply to every row in dataSheet.

Building the Formula for All Rows

1. The basic formula is abstracted using a variable r to represent each row that the BYROW function will pass to the LAMBDA function.
2. r represents an entire row, so
   ** i)**   the INDEX function is used to get the date column which is the first column in each row INDEX(r,,1);
   ii)   the FILTER function is used to FILTER r for all columns except the 1st (date) column FILTER(r, COLUMN(r)<>1);
3. BYROW is added with the range of rows to pass to the LAMBDA function, and the LAMBDA function uses the variable r to represent each individual row passed in BYROW(dataSheet!A:Z, LAMBDA(r,

# Basic Formula

=IFERROR(TEXTJOIN(CHAR(10),TRUE, 
    TEXT(DATEVALUE(dataSheet!A1), "dddd mm/dd"),
    dataSheet!B1:Z1))

# Basic Formula abstracted using 'r' for a row
# INDEX used to get 1st column
# FILTER used to exclude the 1st column

=IFERROR(TEXTJOIN(CHAR(10),TRUE, 
    TEXT(DATEVALUE(INDEX(r,,1)), "dddd mm/dd"), 
    FILTER(r,COLUMN(r)<>1)))

# BYROW and LAMBDA complete the formula

=BYROW(dataSheet!A:Z, LAMBDA(r,
   IFERROR(TEXTJOIN(CHAR(10),TRUE, 
     TEXT(DATEVALUE(INDEX(r,,1)), "dddd mm/dd"), 
     FILTER(r,COLUMN(r)<>1)))
))