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

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Name		Gender	DoB	Age	Name		Gender	DoB	Age	Name		Gender	DoB	Age	Name		Gender	DoB	Age	What I would like!
2	Aerwyna	Witch	(F)	10/31/1978	45	Lub	Lubber	(M)	10/31/1965	58	Prince	Goblin	(M)	8/31/2020	3	Princess	Faerie	(F)	10/31/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
3	Lub	Lubber	(M)	10/31/1965	58	Prince	Goblin	(M)	8/31/2020	3	Princess	Faerie	(F)	10/31/2023	0						Lub Lubber (M) - 31/10/1965, 58 Prince Goblin (M) - 31/08/2020, 3 Princess Faerie (F) - 31/10/2023, 0
4	Prince	Goblin	(M)	9/1/2020	3	Princess	Faerie	(F)	11/1/2023	0											Prince Goblin (M) - 01/09/2020, 3 Princess Faerie (F) - 01/11/2023, 0
Sheet9

Cell Formulas
Range	Formula
T2,J2:J4,E2:E4,O2:O3	T2	=IF(S2="","",DATEDIF(S2,TODAY(),"Y"))
U2:U4	U2	=LET(r,A2:INDEX(A2:T2,COUNTA(A2:T2)),TEXTJOIN(CHOOSE({1,1,2,3,4}," "," - ",", ",CHAR(10)),1,TEXT(r,IF(r<200,"0","dd/mm/yyyy"))))

Make sure you have Wrap Text enabled in that column.

 

[PeteWright]

Turn "Wrap Text" on for this to work

=TEXTJOIN(CHAR(10),TRUE,
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
)

 

[SaraWitch]

Thank you, both!

@Eric W - is there a way to remove that last 0 if there are empty cells (the formula is a bit beyond me to tweak!)?

@PeteWright - yours is almost there too, except it's still returning "- 01/01/1900," if cells are blank...? (Sorry, my mistake in original mini sheet, I meant blank! )

¦ MrExcel Queries.xlsm
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Name		Gender	DoB	Age	Name		Gender	DoB	Age	Household members	What I would like!	@Eric W	@PeteWright
2	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	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
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 anything, i.e. remove: - 00/01/1900,	Lub Lubber (M) - 31/10/1965, 58 Prince Goblin (M) - 31/08/2020, 3 Princess Faerie (F) - 31/10/2023, 0 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,
4											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 anything, i.e. remove: - 00/01/1900, If empty cells in range, don't return anything, i.e. remove: - 00/01/1900,	Prince Goblin (M) - 01/09/2020, 3 Princess Faerie (F) - 01/11/2023, 0 0	Prince Goblin (M) - 01/09/2020, 3 Princess Faerie (F) - 01/11/2023, 0 - 00/01/1900, - 00/01/1900,
Join Text

Cell Formulas
Range	Formula
T2:T4,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
W2:W4	W2	=LET(r,A2:INDEX(A2:T2,COUNTA(A2:T2)),TEXTJOIN(CHOOSE({1,1,2,3,4}," "," - ",", ",CHAR(10)),1,TEXT(r,IF(r<200,"0","dd/mm/yyyy"))))
X2:X4	X2	=TEXTJOIN(CHAR(10),TRUE, 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 )

 

[Eric W]

Sara, that 0 is the age. You don't want that deleted, do you? It was in your sample.

Edit, whoops, sorry, I didn't notice the extra one in your mini-sheet. I suspect it's because there's a non-empty cell in the range. A space that's not showing up for example. Hit "Delete" on any cell in the A:T range that looks blank.

I'll take another look later, but I'm about to head out for a while.

 

[SaraWitch]

I don't want to delete the age, but there's an extra 0 in W3 (line 4) and W4 (line 3). It would be good to get rid of this if it can be done, Eric

 

[PeteWright]

@SaraWitch
Here you go:

=TEXTJOIN(CHAR(10),TRUE,
IF(COUNTBLANK(A2:E2)>0,"",A2&" "&B2&" "&C2&" - "&TEXT(D2,"dd/mm/yyyy")&", "&E2),
IF(COUNTBLANK(F2:J2)>0,"",F2&" "&G2&" "&H2&" - "&TEXT(I2,"dd/mm/yyyy")&", "&J2),
IF(COUNTBLANK(K2:O2)>0,"",K2&" "&L2&" "&M2&" - "&TEXT(N2,"dd/mm/yyyy")&", "&O2),
IF(COUNTBLANK(P2:T2)>0,"",P2&" "&Q2&" "&R2&" - "&TEXT(S2,"dd/mm/yyyy")&", "&T2)
)

Does it work as expected?

 

[Fluff]

A slight tweak to Eric's formula.

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

 

[SaraWitch]

Thank you, all!

Both @PeteWright worked and @Fluff slight tweak to @Eric W.

Wish I could mark you all as a solution, but will do to @PeteWright (only because he was first with the whole solution). But I am really grateful to you all - thank you!

 

[SaraWitch]

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!