worksheet function for returning a field inside a delimited lists
I’d like to have a function available that returns a specified field within a delimited list. I would pass the list, a delimiter and a field number as arguments to the function and it should return the value in that field.
For instance with the pipe separated list ”TEST1|TEST2|TEST3|TEST4| TEST5|||||||TEST|” in A1.
I’d like to use something like GetFieldInDelimitedList(A1;"|";5) and have it return “TEST5”.
Any equivalent formula will requires quite a lot of nested functions e.g.:
I'd like to see two related functions for this:
FIELD(string, delimiter, index) to return a particular field from the delimited list, where positive index counts from the start of the string and negative from the end
NUMFIELDS(string, delimiter) to return the total number of delimited fields from a string
Kenneth Barber commented
I think that Excel would have more functionality if we were to just bring Split over to the sheet side:
Then we can do what we want with the array. INDEX to get a certain element, count, sort, filter for unique, filter based on regular expressions, etc. See:
The proposed Regex UDF should solve this easily.
Kristian Simm commented
Thanks! i actualy made a very similar custom function which i included in my first posting. Anyways in my case inserting a custom vba function wasn't a very good option. A special wbook would have to be shared for this purpose (or instructions for inserting the function)
Andreas Killer commented
Function GetFieldInDelimitedList(ByVal What, _
Optional ByVal Delimiter As String = " ", Optional ByVal Nth As Long = 1)
Temp = Split(What, Delimiter)
GetFieldInDelimitedList = Temp(Nth - 1)