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

SaraWitch

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
322
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUV
1NameGenderDoBAgeNameGenderDoBAgeNameGenderDoBAgeNameGenderDoBAgeHousehold membersWhat I would like!
2AerwynaWitch(F)31/10/197845LubLubber(M)31/10/196558PrinceGoblin(M)31/08/20203PrincessFaerie(F)31/10/20230Aerwyna 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, 0Aerwyna 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
3LubLubber(M)31/10/196558PrinceGoblin(M)31/08/20203PrincessFaerie(F)31/10/20230 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
4PrinceGoblin(M)01/09/20203PrincessFaerie(F)01/11/20230 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
RangeFormula
T2:T4,J2:J4,E2:E4,O2:O3T2=IF(S2="","",DATEDIF(S2,TODAY(),"Y"))
U2:U4U2=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 :)
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Try:

Book1
ABCDEFGHIJKLMNOPQRSTU
1NameGenderDoBAgeNameGenderDoBAgeNameGenderDoBAgeNameGenderDoBAgeWhat I would like!
2AerwynaWitch(F)10/31/197845LubLubber(M)10/31/196558PrinceGoblin(M)8/31/20203PrincessFaerie(F)10/31/20230Aerwyna 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
3LubLubber(M)10/31/196558PrinceGoblin(M)8/31/20203PrincessFaerie(F)10/31/20230Lub Lubber (M) - 31/10/1965, 58 Prince Goblin (M) - 31/08/2020, 3 Princess Faerie (F) - 31/10/2023, 0
4PrinceGoblin(M)9/1/20203PrincessFaerie(F)11/1/20230Prince Goblin (M) - 01/09/2020, 3 Princess Faerie (F) - 01/11/2023, 0
Sheet9
Cell Formulas
RangeFormula
T2,J2:J4,E2:E4,O2:O3T2=IF(S2="","",DATEDIF(S2,TODAY(),"Y"))
U2:U4U2=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.
 
Upvote 0
Turn "Wrap Text" on for this to work

Excel Formula: 
=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
)
 
Upvote 0
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
KLMNOPQRSTUVWX
1NameGenderDoBAgeNameGenderDoBAgeHousehold membersWhat I would like!@Eric W@PeteWright
2PrinceGoblin(M)31/08/20203PrincessFaerie(F)31/10/20230Aerwyna 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, 0Aerwyna 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, 0Aerwyna 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, 0Aerwyna 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
3PrincessFaerie(F)31/10/20230 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 0Lub 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 0Prince Goblin (M) - 01/09/2020, 3 Princess Faerie (F) - 01/11/2023, 0 - 00/01/1900, - 00/01/1900,
Join Text
Cell Formulas
RangeFormula
T2:T4,O2:O3T2=IF(S2="","",DATEDIF(S2,TODAY(),"Y"))
U2:U4U2=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:W4W2=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:X4X2=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 )
 
Upvote 0
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.
 
Last edited:
Upvote 1
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 :)
 
Upvote 0
@SaraWitch
Here you go:
Excel Formula: 
=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?
 
Upvote 0
Solution
A slight tweak to Eric's formula.
Excel 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"))))
 
Upvote 1
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! :biggrin:
 
Upvote 0
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... :unsure:

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! ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
452
Fluff
Fluff
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
299
SaraWitch
SaraWitch
W
  • Solved
Unique with Sort but exluding rows
Replies
4
Views
260
Fluff
Fluff
E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
495
Fluff
Fluff
S
  • Solved
IF function excluding 0 and 9 in text string
Replies
4
Views
213
dreid1011
dreid1011

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top