Struggling to maintain formatting when using textjoin

I have an invoice/quote which is 10 pages long, the main text area of the invoice/quote is made of 3 columns (B, C-G (merged) and H). Column B contains dates or blanks if its an invoice or numeric task list if a quote(if B4="Invoice" or "quote"), Column C is for description and Column H is for cost. To reduce the number of cells to store each invoice on another worksheet(currently 2249 cells wide). I would like to store the data from B27:H54 (page 1 data) in a single cell using textjoin. The problem is the datatype, B=Date formatted as "dd mmm yyyy" or "General" if is quote, C-G = general and H is formatted as currency. All cells may contain data or be blank, but blanks must be kept. Once the formula works I can repeat it for pages 2 to 10.

=TEXTJOIN(",",FALSE,B27:H54) - this works, joining all the cells into one but the date is displayed as 45413 etc

The perfect result would be "Row 27 Date (as "dd mmm yyyy"), Row 27 Description, Row 27 Cost, Row 28 Date, Row 28 Description, Row 28 Cost, Row 29 Date, Row 29 Description, Row 29 Cost" etc
but I could also manage with 3 cells with all the Col 27-54 - ie "Row 27 Date, Row 28 Date, Row 29 Date" "Row 27 Desc, Row 28 Desc, Row 29 Desc" "Row 27 Cost, Row 28 Cost, Row 29 Cost"

I suspect the second result would make loading / saving the data quicker

I have not included all my other more complicated textjoin attempts as the all fail with "#value"

Welcome to the forum.

=TEXTJOIN(",",FALSE,B27:H54) - this works, joining all the cells into one but the date is displayed as 45413 etc

Set the cell formatting to SHORT DATE.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

If you have dates coming from the US, make sure that you are getting the days and months the right way round! US default format is m/d/yyyy.

Try this:

=TEXTJOIN(" ↓ ",1,TEXT(TOCOL(B27:B54,1),"dd/mm/yyyy"))

I think you were pretty close. Try for instance:

Formula:

Please Login or Register  to view this content.

As you declare you are using version 2019 it could require not just Enter but Ctrl+Shift+Enter to commit array formula*

See the attached file and try to edit formula.

HAve a look also on other formulas


PS. TOCOL will probably not work because it's Excel 2019 (I think TOCOL is only available in Excel 365 and web version)


---- out of our canned replies set ----
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).