Forum Discussion

Copper Contributor

Dec 11, 2017

Solved

Excel Array to shorten a very long Function

I need to condense a block (rows and columns) of cells that contain notes into one Notes field. Some cells contain dates and some contain text. I want to maintain the rows and columns of data but c...

Formulas & Functions

Need Help

SergeiBaklan

Dec 13, 2017

Hi Michael,

I guess zero appears for empty cells, you may add one more check if the cell is empty or not

=IF(LEN($B3)=0,0,
   TEXTJOIN(
      " ", FALSE,
      IF(ISNUMBER(INDIRECT("'" & $B3 & "'!C21:G33")),
         TEXT(INDIRECT("'" & $B3 & "'!C21:G33"),"dd/mm/yyyy"),
         IF(ISBLANK(INDIRECT("'" & $B3 & "'!C21:G33")),"",
            INDIRECT("'" & $B3 & "'!C21:G33"))
      ) &
      SUBSTITUTE( {"","","","","lf"}, "lf",CHAR(10))
      )
)

My guess TRUE/FALSE parameter for TEXTJOIN won't work in array formula

SergeiBaklan

Diamond Contributor

Dec 13, 2017

Hi Michael,

Array formula could be like

=IF(LEN($B3)=0,0,
   TEXTJOIN(
      " ", FALSE,
      IF(ISNUMBER(INDIRECT("'" & $B3 & "'!C21:G33")),
         TEXT(INDIRECT("'" & $B3 & "'!C21:G33"),"dd/mm/yyyy"),
         INDIRECT("'" & $B3 & "'!C21:G33")
      ) &
      SUBSTITUTE( {"","","","","lf"}, "lf",CHAR(10))
      )
)

Michael Estler

Copper Contributor

Dec 13, 2017

Sergei, You ROCK!!!

I do have one issue that maybe you can help me clean up. The formula you suggested adds zeros to the text as shown in the sample output below. I believe they are coming from the substitute line of code but I can't seem to make them go away. Any ideas?

Thanks again for working out the array function! I really appreciate your help

sample output from your formula:

0 0 0 0 0
13/05/2016 Sent email to Jeremey 0 0 0
0 Jeremy sent me email back and we are sendng samples to him on Monday 0 0 0
0 Follow up on Wed. 0 0 0
0 0 0 0 0
23/05/2016 left message for Jeremy 0 0 0
0 c/b if message not returned 0 0 0
0 0 0 0 0
21/06/2016 Sent follow up email to Jeremy 0 0 0
0 0 0 0 0
11/07/2016 Sent another follow up to jeremy 0 0 0
0 0 0 0 0
15/07/2016 Jeremey is on Paternity leave. Vheck back Aug 0 0 0
0 0 0 0 0
11/08/2016 Left Jeremy a message 0 0 0
0 0 0 0 0
19/09/2016 Sent jeremy a follow up email 0 0 0
16/10/2016 Ron called - no response 0 0 0
23/11/2016 Nail down Jeremy!!! 0 0 0
01/12/2016 Try again n new year 0 0 0
0 0 0 0 0
17/01/2017 Left a vm for Jeremy. Spoke to reception. He didn’t take my call 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0

SergeiBaklan
Diamond Contributor
Dec 13, 2017
Hi Michael,

I guess zero appears for empty cells, you may add one more check if the cell is empty or not

=IF(LEN($B3)=0,0, TEXTJOIN( " ", FALSE, IF(ISNUMBER(INDIRECT("'" & $B3 & "'!C21:G33")), TEXT(INDIRECT("'" & $B3 & "'!C21:G33"),"dd/mm/yyyy"), IF(ISBLANK(INDIRECT("'" & $B3 & "'!C21:G33")),"", INDIRECT("'" & $B3 & "'!C21:G33")) ) & SUBSTITUTE( {"","","","","lf"}, "lf",CHAR(10)) ) )

My guess TRUE/FALSE parameter for TEXTJOIN won't work in array formula
- Michael Estler
  Copper Contributor
  Dec 13, 2017
  Thanks again Sergei!
  not only did you solve my problem i think i learned a little bit about INDIRECT and SUBSTITUTE and Arrays