Hello everyone. I’m looking for assistance with the CONCATENATE function in Google Sheets. I’ve noticed that when I use this function, it still includes separators like semicolons even if some cells are empty. This results in unwanted additional semicolons in my final output.
For example, if I concatenate:
- Cell A1: “Alice”
- Cell B1: (empty)
- Cell C1: “Johnson”
Using CONCATENATE(A1,“;”,B1,“;”,C1) gives me the output “Alice;;Johnson” when I actually want it to just show “Alice;Johnson”.
Is there a method to make CONCATENATE ignore these separators when the related cells are empty? I’d appreciate any tips that work regardless of which cells are blank.
you can also use the JOIN function - it’s like TEXTJOIN but simpler. try JOIN(“;”, A1, B1, C1) and it’ll automatically skip empty cells. I started using this back when TEXTJOIN wasn’t available in older sheets versions and still use it sometimes cuz it’s more straightforward.
Had this exact problem six months ago building a client database. TEXTJOIN is way cleaner than CONCATENATE for this. It automatically skips empty cells and won’t add separators for them. Just use TEXTJOIN(“;”, TRUE, A1:C1) - the TRUE part tells it to ignore empties. Completely fixes the double separator issue you’re seeing. You could stick with CONCATENATE and wrap each cell in an IF statement to check if it’s empty, but TEXTJOIN’s much more elegant and saves tons of time with multiple cells.
You can also use FILTER with CONCATENATE if you want more control. Create a helper column with FILTER(A1:C1,A1:C1<>“”) to exclude empty cells, then apply CONCATENATE on that filtered range. This method is particularly useful for dynamic datasets where blanks appear randomly. I frequently use it with imported data that has inconsistent gaps. FILTER constructs a clean array without holes, ensuring your separators only appear between actual values. Although it requires more setup than TEXTJOIN, it offers significantly greater flexibility for complex scenarios.