1

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?

3

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

  • Thank you! Is there a way to set up the 1st option with an arrayformula? The data I'm combining is coming in from a Google form and I want it to auto combine as new forms are submitted. Thanks again! – Mr. B Aug 8 '16 at 21:15
  • 1
    Yes, edited in. – user79865 Aug 8 '16 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.