0

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 2

1

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.

0

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,","))))
1
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.