Hi everyone! I’m working with the CONCATENATE function in Google Sheets and running into an issue. When I try to combine multiple cells with semicolon separators, the separators still show up even when some of the cells are empty. This creates unwanted semicolons in my final result. For example, if I have a formula like =CONCATENATE(A1,";",B1,";",C1) and cell B1 is empty, I get something like “value1;;value3” instead of “value1;value3”. Is there a way to make the separators only appear when there’s actually content in the cells? I need to clean up my data and remove these extra delimiters. Any suggestions would be really helpful!
I’ve hit this same issue tons of times. Wrapping each cell in an IF statement gives you tight control: =CONCATENATE(IF(A1<>"",A1&";",""),IF(B1<>"",B1&";",""),IF(C1<>"",C1,"")). You’ll have to deal with that trailing semicolon though. Gets ugly fast when you add more cells. Now I just use a helper column to flag which cells have data, then only pull those into the concatenation. More setup work, but way more flexibility with formatting and much easier to debug when things get messy.
try using the JOIN function instead - it automatically skips empty cells! just do =JOIN(";", A1:C1) and it won’t add semicolons for blank cells. way cleaner than concatenate imo
Try TEXTJOIN if you want more control over the output. Use =TEXTJOIN(";", TRUE, A1:C1) - the TRUE part ignores empty cells automatically. I use this all the time for larger ranges or when I need different delimiters. TEXTJOIN beats JOIN because you can pick exactly which cells to include and it handles mixed data types way better. Both are miles better than those nested IF statements that turn into a complete mess.
This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.