There are various numbers of comma separated values in a Google Sheets column, how can I retain no more than the first 6 values in each cell & discard the rest?
2 Answers
If the google sheet column is column A, you could try this formula:
=index(
ifna(
if(
len(A1:A)-len(substitute((A1:A), ",", ""))+1>=6,
regexextract(substitute(A1:A,",","§",6),"(.*)§"),
A1:A
)
)
)
where:
len(A1:A)-len(substitute((A1:A), ",", ""))+1>=6
checks that there are at least 6 commas in the cell and, if not, leaves its content unchanged.
Another approach for comma separated variables in column A (but less elegant then the first answer):
=arrayformula(lambda(exploded,lambda(maxcols,filter(exploded,sequence(1,maxcols)<=6))(
columns(exploded)))(
iferror(split(A:A,","))))
answered Dec 26, 2022 at 10:18
-
Thanks for the replies, I tried both formulas. Not knowing how to use them I inserted a row at the top of the sheet & placed the formula in the empty cell at the top of column E. The A's in the formula were replaced with E's, I got this popup error message: "Error Array result was not expanded because it would overwrite data in E3." Dec 28, 2022 at 13:12