Combine cell data, including dates and times, into one text cell

Question

I'm trying to figure out how to combine many cells into one cell in a Google Sheet. The target cells contain many different types of data (date, time, text, numbers, etc.) and I need to merge them into one cell so they appear as displayed in the target cells.

I need to be able to combine the following into one cell.

Col A     Col B     Col C     Col D     Col E
text      date      time      text      time

I can use =A1&B1&C1&D1&E1, but it only works with text. When it gets to date or time data, it displays random numbers instead of a date or time.

Is there a method for copying the data in the cells (the way it is displayed) and merging it into one?

Answer 1

To convert dates or times to text as shown, use to_text, e.g.,

= A1 & to_text(B1) & to_text(C1) & D1 & to_text(E1)

You can do this for every row at once:

=arrayformula(A1:A & to_text(B1:B) & to_text(C1:C) & D1:D & to_text(E1:E))

---

Within one row, one can use join with an appropriate delimiter like "", ", " or " ":

=join(" ", arrayformula(to_text(A1:E1)))

(Applying to_text has no effect when the argument is already text.)

But this does not work for joining every row at once.

---

If you wanted dates/times to convert them in a specific format regardless of how they are displayed in the sheet, that would be done with text, for example =text(B1, "m/d/yyyy").