Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 31, 2019

Lisa LaCombe 

If you are appending fields individually with "&" (I didn't realise TEXTJOIN was so recent; up to about a month ago I was using Office 2010 so hadn't really noticed its release date) you could reduce space demands by using ", " to separate the Room and Unit codes, so placing them on the same line.

I would probably use FILTER to select multiple records but that is little use to most others.

 

Correction

I have just found that TEXTJOIN accepts an array as delimiters so comma separator and line feeds can be combined!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 31, 2019

PeterBartholomew1 

Peter, they are in one line here and separated with "/"

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 02, 2019

    SergeiBaklan 

    I am basically playing but I found a TEXTJOIN delimiter to be the named formula

    =CHAR({10,47,47,10,10})

    worked well in the formula

    = IFERROR( TEXTJOIN( delimiter, TRUE,

    VLOOKUP( CurrentDate, ImportantDates, {2,3,4,5,7}, FALSE ) ),

    "")

     

    Doe, Jane
    128B/1B/43632
    30 Day Evaluation
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 03, 2019

      PeterBartholomew1 ,

       

      Yes, that's much better. I'd only use as delimiter

      =SUBSTITUTE({"X"," / ","X","X","X--------X"},"X",CHAR(10))

      not to limit delimiter by one character. Plus VLOOKUP returns only one (first found) record. Finally

      =TEXTJOIN(delimeter,TRUE,
   IF(ROW(Important_Data_Only)*(Important_Dates_Only=Current_Date)*(Important_Data_Only<>""),
      Important_Data_Only,""))

Resources