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

[SaraWitch]

Hello peeps,

I have a formula to combine text and return dates where there is one, which works just as I want it to, except I don't want anything to return for blank cells. I know I could probably use CONCAT or JOINTEXT (but I couldn't find a way of returning dates in my range and I want different separators). I would also like to start each person on a new line (I tried &CHAR(10) but that didn't work). I also know that I can adjust the column width, but I have a large dataset that won't fit nicely into a determined width.

¦ MrExcel Queries.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Name		Gender	DoB	Age	Name		Gender	DoB	Age	Name		Gender	DoB	Age	Name		Gender	DoB	Age	Household members	What I would like!
2	Aerwyna	Witch	(F)	31/10/1978	45	Lub	Lubber	(M)	31/10/1965	58	Prince	Goblin	(M)	31/08/2020	3	Princess	Faerie	(F)	31/10/2023	0	Aerwyna Witch (F) - 31/10/1978, 45 Lub Lubber (M) - 31/10/1965, 58 Prince Goblin (M) - 31/08/2020, 3 Princess Faerie (F) - 31/10/2023, 0	Aerwyna Witch (F) - 31/10/1978, 45 New line Lub Lubber (M) - 31/10/1965, 58 New line Prince Goblin (M) - 31/08/2020, 3 New line Princess Faerie (F) - 31/10/2023, 0
3	Lub	Lubber	(M)	31/10/1965	58	Prince	Goblin	(M)	31/08/2020	3	Princess	Faerie	(F)	31/10/2023	0						Lub Lubber (M) - 31/10/1965, 58 Prince Goblin (M) - 31/08/2020, 3 Princess Faerie (F) - 31/10/2023, 0 - 00/01/1900,	Lub Lubber (M) - 31/10/1965, 58 New line Prince Goblin (M) - 31/08/2020, 3 New line Princess Faerie (F) - 31/10/2023, 0 If empty cells in range, don't return, i.e.: - 00/01/1900
4	Prince	Goblin	(M)	01/09/2020	3	Princess	Faerie	(F)	01/11/2023	0											Prince Goblin (M) - 01/09/2020, 3 Princess Faerie (F) - 01/11/2023, 0 - 00/01/1900, - 00/01/1900,	Prince Goblin (M) - 31/08/2020, 3 New line Princess Faerie (F) - 31/10/2023, 0 If empty cells in range, don't return, i.e.: - 00/01/1900, If empty cells in range, don't return, i.e.: - 00/01/1900,
Join Text

Cell Formulas
Range	Formula
T2:T4,J2:J4,E2:E4,O2:O3	T2	=IF(S2="","",DATEDIF(S2,TODAY(),"Y"))
U2:U4	U2	=A2&" "&B2&" "&C2&" - "&TEXT(D2,"dd/mm/yyyy")&", "&E2&" "&F2&" "&G2&" "&H2&" - "&TEXT(I2,"dd/mm/yyyy")&", "&J2&" "&K2&" "&L2&" "&M2&" - "&TEXT(N2,"dd/mm/yyyy")&", "&O2&" "&P2&" "&Q2&" "&R2&" - "&TEXT(S2,"dd/mm/yyyy")&", "&T2

Any help would be greatly appreciated

 

[Fluff]

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

 

[Eric W]

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...

=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!

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.

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

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.

 

[SaraWitch]

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!!