3

I keep running into situations where I have a list of values in a cell in Google Spreadsheets, and I need some value out of the list: by list I mean a string of values (generally dates, but could be numbers or some other data) and I want the first out of the list. I use this:

=split(substitute(A1,char(10),","),",")

In principle, that produces an array (range?) of values, but I find that if I use the above in a formula that requires a single value/cell I can use it to access the first value.

Is there a way to access the last value of such a list (common delimiters of the list could be char(10) or comma)?

Lists are of variable length.

1

This should allow you to get the last element in the array generated by split():

=ArrayFormula(index(split(A1,char(10)), columns(split(A1,char(10)))))
1

Shorter:

=index(split(A1,"|"),counta(split(A1,"|")))

Assumes pipe is the relevant delimiter, but may be changed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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