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

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
378
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 :)
 
Without being able to see your data & without knowing how it's not working, it's very difficult to help.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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! ;)

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.

Excel Formula:
=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.
 
Upvote 0
Of course! Sorry, Fluff. ...And with fresh eyes today, I realise that I had put an extra bracket where it wasn't needed :rolleyes:, 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!! 😍
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

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