Search code examples
emailjoingoogle-sheetssplitarray-formulas

SPLIT() then JOIN() and populate column with ARRAYFORMULA()

I am trying to Split email addresses in column E2:E on the "dot" operator and then Join the names back into a single cell. I have written the below formula enclosed with ARRAYFORMULA() to populate the cells in Column A. But i keep getting the following error:

JOIN range must be a single row or a single column.

=ARRAYFORMULA( IF( E2:E = "", "", JOIN( " ", SPLIT( SUBSTITUTE( FILTER( E2:E, LEN( E2:E ) > 0 ), "@abc.com", "", 1 ), ".") ) ) )

Is there a workaround solution?

Solution

  • I think i found the answer:

    =ARRAYFORMULA(IF(E2:E="","",TRANSPOSE(QUERY(TRANSPOSE(SPLIT(SUBSTITUTE(FILTER(E2:E,LEN(E2:E)>0),"@ABC.COM","",1),".")),,50000))))