Google-sheets – Find last value in list in cell

google sheets

I keep running into situations where I have a list of values in a single cell, 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) separated commonly by Ctrl+Enter (char(10)) and I want the first out of the list. I use this:

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

In principle, that produces an array 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.

Best Answer

Shorter:

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

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