Join text and return dates where there is one but ignore if empty cells

Without being able to see your data & without knowing how it's not working, it's very difficult to help.

SaraWitch said:

I've just tried to replicate the formulas in my dataset and @PeteWright is working (I've got additional columns so have just added them to the formula).

However, the other is not when I update the range (my columns are H:AK). This formula is a little beyond my understanding, so maybe it needs more than just that tweak...

VBA Code:

=LET(r,FILTER(H3:AK3,H3:AK3<>""),TEXTJOIN(CHOOSE({1,1,2,3,4}," "," - ",", ",CHAR(10)),1,TEXT(r,IF(r<200,"0","dd/mm/yyyy"))))

Not to worry too much because, like I say, @PeteWright is working... ...just a little curious about the other because I'm always learning!

Click to expand...

LIke Fluff said, it's hard to troubleshoot without seeing your sheet. If you could post another mini-sheet, that would help. But let me give you a breakdown of the formula, because it's not too hard.

The LET function lets you define intermediate results so you can use them multiple times, without having to type them all out every time. In this case, I defined the variable r (which is short for "range") to contain the cells that have data in them and the FILTER function gets rid of empty cells, leaving only your data. (Thanks Fluff for your improvement!)

Next, it's a little known fact that TEXTJOIN can use more than one delimiter. If you define more than one in an array, TEXTJOIN will cycle through them and repeat as necessary. So for your data, you need an array of {" "," "," - ",", ",CHAR(10)}. (Space before last name, space before gender, dash before DoB, comma before age, CHAR(10) at the end of a line. Sadly, you can't put CHAR(10) in an array like that, so I used the CHOOSE function to create that array. You actually can create the array without CHOOSE, by using Alt-Enter for the carriage return. But that's very easy to miss.


Finally, we need to format each element so that it looks right. The only one that really gives us a problem is the DoB. We can use TEXT(r,"dd/mm/yyyy") to format it as a date and not the serial number. The other text values work fine with that too. But then we run into a problem with the age. The age will try to format as a date, which won't look right. Since ages should be under 200, and date serial numbers should be over 200, the IF(r<200, . . .) function decides what format to use.

So in short: get the non-empty cells, create the delimiter array, format the elements according to type, then TEXTJOIN puts them all together.

Your update should work based on just looking at it, but if you can show an example of why it's not working, we'll take a look at it.

Of course! Sorry, Fluff. ...And with fresh eyes today, I realise that I had put an extra bracket where it wasn't needed , so it's working perfectly!!

And, wow, Eric! Thank you so much for the explanation - this really helps with my learning! This is definitely going in my 'Excel Tips and Learning' workbook!

Again, thank you all for your help - this forum is invaluable!!