Google-sheets – Find a cell with text in a range and return the text in that cell

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

Try this:

 =ARRAYFORMULA(SUM(--REGEXEXTRACT(A1:A6,"(\d+)")))