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

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.

• Thank you that worked perfectly! I would upvote but unfortunately do not have enough reputation. Thanks again! Feb 28, 2023 at 23:48
• @iceninja21 you can "accept" the answer after you wait a bit for better answers Mar 1, 2023 at 0:37
• @jsotola Oh I see. Thanks! Mar 1, 2023 at 0:38
• No worries. I upvoted him for you. Mar 1, 2023 at 4:02

# 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)
``````

Basic Formula (Step 1 of 2)

Source Data

#### 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)))
))
``````
• Thank you for your answer. I unfortunately cannot upvote because I don't have the reputation and I cannot accept the answer because I am using the solution mentioned above. That said, your answer definitely gives me things to think/learn about and further my knowledge in spreadsheets. Thank you! Mar 1, 2023 at 3:57