I need google spreadsheet formula which will allow me to comma separation items, im…
- User
I need google spreadsheet formula which will allow me to comma separation items, im using =join(", ";E2:E3) but this is not ignoring empty cells, please modify it for me to do so
- Assistant
To create a formula in Google Sheets that joins cells with a comma and ignores empty cells, you can use the
TEXTJOINfunction, which is designed for such tasks. TheTEXTJOINfunction includes an option to ignore empty cells.Here's how you can modify your formula:
=TEXTJOIN(", ", TRUE, E2:E3)In this formula:
- The first argument
", "is the delimiter, which in your case is a comma followed by a space. - The second argument
TRUEtells the function to ignore empty cells. - The third argument
E2:E3is the range of cells you want to join.
This formula will join the contents of cells E2 and E3, separating them with a comma and a space, but it will ignore any cells that are empty. You can extend the range (
E2:E3) as needed to include more cells. - The first argument
- User
how can i change it to join text from one column and after this next one?
- Assistant
If you're looking to join text from one column and, after that, include text from another column, all while separating items with a comma and ignoring empty cells, you essentially want to concatenate the ranges and then join them. Google Sheets doesn't natively support directly concatenating ranges in a single formula without some workaround.
One approach to achieve this, assuming you want to join texts from column E and then column F (adjusting for larger ranges or different columns as needed), is to use a combination of
ARRAYFORMULA,FILTER, and thenTEXTJOIN. Here's how you would do it:=TEXTJOIN(", ", TRUE, FILTER({E2:E; F2:F}, {E2:E; F2:F} <> ""))Breaking down this formula:
-
{E2:E; F2:F}: This part creates a new virtual array ("array literal" in Google Sheets parlance) that consists of all the cells inE2:Efollowed by all the cells inF2:F. The semicolon;is used to stack the F column range below the E column range, essentially concatenating the columns vertically in the array. -
FILTER({E2:E; F2:F}, {E2:E; F2:F} <> ""): This part filters out any empty cells from the concatenated array. -
TEXTJOIN(", ", TRUE, ...): This joins all the non-empty cells in the filtered array, separated by a comma and a space, ignoring any empty cells (the second parameterTRUEindicates to ignore them).
This formula effectively allows you to join the texts from E and F columns, in order, separated by commas, while excluding empty cells. Adjust the ranges
E2:EandF2:Fas necessary to fit your actual data range.Keep in mind the overall readability and performance implications when working with very large datasets, as complex array operations can slow down your spreadsheet.
-
- User
i want universal one, cause my range is changing
- Assistant
For a more universal solution that adapts to changing ranges within your two specified columns, you can still rely on the
TEXTJOINandFILTERfunctions, but with slight modifications to accommodate dynamic range selections. Unfortunately, Google Sheets doesn't allow for truly dynamic referencing in the way some might want (e.g., automatically adjusting to the exact number of filled rows in both columns without manual updates), without using Google Apps Script. However, you can use a workaround for a relatively large range that likely encompasses any data you have (assuming it doesn't span the entire available range in Google Sheets).Here’s a formula that you can use assuming you’re working with columns E and F, and you select a range large enough to cover any potential expansions of your data:
=TEXTJOIN(", ", TRUE, FILTER(FLATTEN({E:E; F:F}), FLATTEN({E:E; F:F}) <> ""))Here,
FLATTENis used to create a single column array from your specified ranges (columns E and F,E:EandF:Fin this example). This effectively handles them as one long column. TheFILTERpart then excludes any empty cells from this combined array. Finally,TEXTJOINis used to combine these elements into a single text string, separated by commas (and spaces, after the comma).Key Points:
-
FLATTENisn't an officially documented Google Sheets function as of my last update, yet it works to transform a two-dimensional array into a one-dimensional one, which can be very handy in cases like this. Use it cautiously, as unofficial features might not be supported permanently or could behave unpredictably. -
Dynamic Ranges: While you can use
E:EandF:Fto cover entire columns, be mindful that this includes a vast number of empty cells at the bottom of your sheet. This shouldn't be a problem with theFILTERfunction removing empty entries, but it can potentially increase calculation times if your spreadsheet is already complex or slow.
If Your Spreadsheet Grows Very Large
If your spreadsheet is very large or if you experience performance issues, consider a Google Apps Script or setting a more concrete upper limit in your ranges (e.g.,
E1:E1000instead ofE:E, adjusted according to your data size).Note on
FLATTENSince
FLATTENis not officially supported and documented, its future availability can’t be guaranteed. An alternative approach would be using scripts or manually adjusting ranges if necessary. For scripts, you'd have to write a custom function that reads the range dynamically and concatenates non-empty cells, but this goes beyond the simplicity of formula solutions and requires a bit of coding in Google Apps Script. -