Ask Your Question

Calc: Combine cells but keep formatting

asked 2020-07-30 20:27:01 +0200

Avaviel gravatar image

updated 2020-07-30 20:28:41 +0200

I need to be able to output the text of cells into a single field I can copy and paste elsewhere, and keep the formatting of the previous field. As in, I'd have a date, time, text string and new line character then text string, text string, text string. So there are separate cells of data from where ever, and then an output a single cell looking like this:

07/30/20 08:00 PM Name of Event (newline) Team Vs Team

edit retag flag offensive close merge delete



What is the difficulty? An incomprehensible number instead of a date? Use the TEXT() function. Don't know how to insert a "new line"? Use the function CHAR(10). Don't know how to concatenate individual strings? Use CONCATENATE(), CONCAT() or ampersand.

JohnSUN gravatar imageJohnSUN ( 2020-07-30 20:38:51 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-07-30 20:59:31 +0200

LeroyG gravatar image

updated 2020-07-30 20:59:43 +0200

If data are in the columns A to F, put in G =TEXTJOIN(" ";0;TEXT(A1;"mm/dd/yy");TEXT(B1;"HH:MM AM/PM");C1)&CHAR(10)&TEXTJOIN(" ";0;D1;E1;F1).

In this case it seems to me better to use TEXTJOIN to avoid a few &" "&.

Sample file.

More LibreOffice Help on TEXTJOIN and on TEXT.

@JohnSUN answer, that I just saw, is shorter and simpler: =TEXT(A1;"mm/dd/yy")&" "&TEXT(B1;"HH:MM AM/PM")&" "&C1&CHAR(10)&D1&" "&E1&" "&F1.

edit flag offensive delete link more


thank you! I'll give those a try. I had looked up the TEXT and other commands but didn't fully understand the syntax.

Avaviel gravatar imageAvaviel ( 2020-07-31 00:58:51 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-30 20:27:01 +0200

Seen: 22 times

Last updated: Jul 30