Hi. First time posting.
I am using the following formula to concatenate a series of dates:
=IF($D$5="","","The expiration date(s) for all active(s) is(are): "&SUBSTITUTE(SUBSTITUTE(TRIM($D$9&" "&$U$9&" "&$AL$9&" "&$BC$9&" "&$BU$9&" "&$CL$9&" "&$DC$9&" "&$DT$9&" "&$EL$9&" "&$FC$9&" "&$FT$9&" "&$GK$9&" "&$HC$9&" "&$HT$9&" "&$JM$9&" "&$JB$9)," ","; "),";;","; "))
The result is:
|
The expiration date(s) for all active(s) is(are): 43890; 43900; 43353
When I use Number/Format/Date and choose a date format, nothing happens. When I did that on the individual cells in the formula, the serial numbers converted to the date format I wanted. I converted the original data to the same date format and it still didn't work. I even tried the formula without the initial text string and no change in the result. Any suggestions? Thank you very much, Marty
|
Hi Marty,
When you combine text and numbers in one cell like you do here Excel treats it as text, even the numbers.
Below is an example of how you can do to fix this. You can check the attached file for more details.
What you need to do is in a new row (or sheet for that matter) fetch the values from cells D9 to JB9 and format as text using TEXT function. In below example I use row 11, cells D11 to JB11. That formula would then be =TEXT(TRIM(D$6),"yyyy-mm-dd") to fetch the date serial number from cell D9 and convert it to text formatted as date.
Your new formula to show the result will be much shorter and instead of SUBSTITUTE you use TEXTJOIN. The new formula would then be =IF($D$5="","","The expiration date(s) for all active(s) is(are): "&TEXTJOIN("; ",TRUE,D11:JB11)).
Check out the attached file and you will see how it works.
Br,
Anders
Hi Anders. Thank you very much for your help. Unfortunately, TEXTJOIN is not an option in my version of Excel (2010). When I started to type in TEXTJOIN, there were no other functions listed following TEXT. Looks like I may still have to use SUBSTITUTE for the dates I converted to text following your suggestion.
Thanks once again,
Marty
Hi Marty,
True, TEXTJOIN came with Excel 2016 version. I hope you get it to work as you want it to be.
Br,
Anders
