How to copy or CONCATENATE complex string within Spreadsheet

user_1316842 0
Viewed 39

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

enter image description here

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.

spreadsheet google-sheets
2 Answers

An option would be to use an Apps Script Custom Function.

First, open a bound script by selecting Tools > Script editor, and copy the following function to the script:

function CONCAT_WITH_DATES(rangeNotation) {
  const values = SpreadsheetApp.getActiveSheet()
                               .getRange(rangeNotation)
                               .getDisplayValues()
                               .map(row => row.filter(cell => String(cell)))
                               .map(row => row.join("     "))
                               .join("\n");
  return values;
}

Once the script is saved, you can use this function the same you would use any sheets built-in function:

enter image description here

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:

user_106120110

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))
user_161251390
Related