Hi everyone, I’m working with Google Sheets and running into an issue with the CONCATENATE function. When I use it to combine multiple cells with semicolon separators, the separators still show up even when some of the cells are empty. This creates ugly results like “value1;;;value4” instead of “value1;value4”. I need to find a way to hide or skip the semicolon delimiters when the corresponding cells are blank. Has anyone dealt with this before? I’ve tried a few approaches but can’t seem to get it working properly. Any suggestions on how to make the CONCATENATE function smarter about handling empty cells would be really helpful. Thanks in advance!
Had the same issue last month building a client reporting system. Manual formulas turn into a nightmare with dynamic data.
Skipped the complex IF statements and array formulas - automated everything instead. Built a flow that grabs data from Google Sheets, strips out empty values, then concatenates with the right separators.
Best part? You can add logic for different separator rules, clean data before concatenating, and format however you want. Runs automatically when your sheet updates.
Saved hours of debugging and never breaks. Way better than nested IFs that explode when you add columns.
Array formulas with IF statements work great too. I ran into this same issue when merging address fields for mail merge. Here’s the trick - wrap your concatenate like this: =ARRAYFORMULA(CONCATENATE(IF(A1<>“”,A1&“;”,“”),IF(B1<>“”,B1&“;”,“”),IF(C1<>“”,C1&“;”,“”))) then use REGEXREPLACE to strip any trailing semicolon. More setup than TEXTJOIN but you get exact control over each field. Super helpful when different cells need different rules or you’re dealing with messy imported data.
if you’re stuck with older sheets, try JOIN with FILTER: =JOIN(“;”,FILTER(A1:E1,A1:E1<>“”)). it’s clunky but works when textjoin isn’t available. I’ve used this on legacy sheets at work
Had the same issue when merging customer data across columns. Use TEXTJOIN instead of CONCATENATE - it skips empty cells automatically. Just do TEXTJOIN(“;”, TRUE, A1:E1) where TRUE ignores blanks. No more annoying double separators. If you don’t have TEXTJOIN, try combining FILTER and JOIN but it’s messier. TEXTJOIN’s been a lifesaver since I found it. Way better than writing tons of IF statements for each cell.