3

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.

1

Shorter:

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

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

| improve this answer | |
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)))))
| improve this answer | |

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.