How to copy or CONCATENATE complex string within Spreadsheet-CodePudding

I've created a spreadsheets consist of multiple complex dates & strings.

When I use concatenate, the date become numbers

=CONCATENATE('2021'!A2:'2021'!F61)

The results is like

44519in44471out44472Guest1in44475out44476SGuest2

How can I produce the result like this in one cell

November 2021                                        
in        6/11        out        10/11 Guest 1
in        26/11        out        28/11 Guest 2        
December 2021                                        
in        29/12        out        31/12 Guest 3

I just need the copy & paste cell features, but I can't find any.

CodePudding user response：

An option would be to use an Apps Script

Note:

The input range has to be provided in quotes in order to retrieve the values with the displayed format via Range.getDisplayValues(). If the range is provided directly, the date values won't keep the format.

Reference:

Custom Functions in Google Sheets

CodePudding user response：

Please note that an extra column F, which should be blank, is selected in order to identity row break

=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(TEXTJOIN("♦",FALSE,'2021'!A1:F5),"♦{2,}",CHAR(10)),"♦$",),"♦",CHAR(9))