yogi_Combine Row By Row Non-Blank Entries In Columns A to D With A Hyphen

Google Spreadsheet   Post  #2324

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-18-2017

question by: Ryan

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/34iQzzOcSfY;context-place=forum/docs

Conditional Concatenate Down a Column

Alright community, I'm conceding that I may have been out of the GSheets game for too long and have lost the edge!

I have a spreadsheet where some columns have numbers and alphanumeric sets, and some are blank. I'd like to concatenate down the whole column, adding a hyphen between each, but only if there is data in the cells. The function will be implemented on a Form Response sheet so I don't want to have to autofill down the column. I'd like to just have one function.

I can easily do this with =ARRAYFORMULA(A2:A&"-"&B2:B&"-"&C2:C&"-"&D2:D) but of course it will add unnecessary hyphens when there are blank cells in a row.

So I tried this in my working sheet...

=IF(AND(ISBLANK(J3)=FALSE,ISBLANK(K3)=FALSE,ISBLANK(L3)=FALSE,ISBLANK(M3)=FALSE),J3&"-"&K3&"-"&L3&"-"&M3,IF(AND(ISBLANK(J3)=FALSE,ISBLANK(K3)=FALSE,ISBLANK(L3)=FALSE,ISBLANK(M3)=TRUE),J3&"-"&K3&"-"&L3,IF(AND(ISBLANK(J3)=FALSE,ISBLANK(K3)=FALSE,ISBLANK(L3)=TRUE,ISBLANK(M3)=TRUE),J3&"-"&K3,"")))

...Which works but I have to autofill down the whole column. So then I tried this...

=IF(AND(ISBLANK(J3:J)=FALSE,ISBLANK(K3:K)=FALSE,ISBLANK(L3:L)=FALSE,ISBLANK(M3:M)=FALSE),ARRAYFORMULA(J3:J&"-"&K3:K&"-"&L3:L&"-"&M3:M),IF(AND(ISBLANK(J3:J)=FALSE,ISBLANK(K3:K)=FALSE,ISBLANK(L3:L)=FALSE,ISBLANK(M3:M)=TRUE),ARRAYFORMULA(J3:J&"-"&K3:K&"-"&L3:L),IF(AND(ISBLANK(J3:J)=FALSE,ISBLANK(K3:K)=FALSE,ISBLANK(L3:L)=TRUE,ISBLANK(M3:M)=TRUE),ARRAYFORMULA(J3:J&"-"&K3:K),"")))

...Which pretty much yielded desired results except that it only formats based on the first row of data it sees so the rest of the column is only correct in the instances that the row's contents match the first row of data.

Anyway, I'm stumped and need some help. I saw this post but I don't really understand the construction of the solution and therefore don't know if it will apply to my sheet.

Thanks in advance!