Jtdylktuy

Selecting a specific value from a comma-delimited list can be performed with the CHOOSE function. For example:

=CHOOSE(A1,"A","B","C","D")

where A1 contains the index value, and "A","B","C","D" is the list.

However, CHOOSE only works on an actual list embedded in the function, not a reference to a list.

Suppose you have a situation where the list is dynamic, and created and stored in a cell. Say right now, cell B1 contains the comma delimited string "A","B","C","D", and it might be a different list at another time.

The requirement is still to select from that list based on the index value in A1, so the equivalent to the pseudo-code:

=CHOOSE(A1,CONTENTS(B1))

Is there a way to accomplish the equivalent of that?

• I'm looking for a generic approach. The list could be anything and any number of terms, which precludes a solution tied to a specific number of items, or items of a particular type or format. It needs to handle the general situation of any list conforming to the CSV standards. However, the list will not be so large as to exceed any Excel limitations.


• Note that this is different from the action of the INDIRECT function, which won't work for this purpose.


• The solution needs to behave like a function (automatically stay current with changing content). That precludes a solution requiring manual intervention. An automatically triggered VBA solution would not be precluded if that is the only possible solution, but it is undesirable because VBA would not always be available, and it would limit the ability to transfer the solution to other spreadsheet applications.


• Use of a helper column is not precluded, but the variable nature of the data would make it impractical for a solution that involves parsing the list into separate cells.

This is an old question, and an example of an XY problem. I was too focused on the method to recognize that there was another way to get to the result. After reframing the question, I realized this can be accomplished with some standard formulas.

The task is really to extract the Nth segment of text based on comma delimiters. You just need to locate the relevant commas and use the MID function.

Assume cell A1 contains the index (N), and cell B1 contains the comma delimited list. A standard method to locate the Nth occurrence of a character (in this case, a comma), is:

=FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1))

CHAR(1) is just a character that won't be part of any cell content. The SUBSTITUTE function has an optional fourth parameter to define the specific occurrence to substitute. This formula replaces the Nth occurrence (cell A1) of a comma with CHAR(1), and then finds it.

The first and last list value have a comma on only one side, so this formula needs to be expanded in order to use the MID function. To find the starting location of the Nth list item, we can use:

=IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1)

Note that the starting point for item 2 is in reference to the first comma, so the relevant comma to locate is N-1. The first character of the item is +1 position after the comma.

To find the ending location of the last list item, we can use the LEN function, we just need to identify that it's the last item. The number of list items will be one more than the number of delimiters. We can get a count of commas with:

=LEN(B1)-LEN(SUBSTITUTE(B1,",",""))

This replaces all commas with the zero-length null and then finds the difference in the string length. The difference is the number of commas.

After specifying the starting point, MID uses the length of the text to be extracted. That's found by subtracting the starting point from the position after the end of the list item, which will be either the position of the next comma, or for the last item, the position where the next comma would be, one character after the length of the string. So the measurement endpoint would be:

=IF(A1>(LEN(B1)-LEN(SUBSTITUTE(B1,",",""))),LEN(B1)+1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1)))

The length parameter is found by subtracting the item starting location from the above measurement point. Putting it all together with the MID function yields:

=MID(B1,IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1),IF(A1>(LEN(B1)-LEN(SUBSTITUTE(B1,",",""))),LEN(B1)+1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1)))-IF(A1=1,1,FIND(CHAR(1),SUBSTITUTE(B1,",",CHAR(1),A1-1))+1))

This formula just extracts the item. If, like in this example, the item is a text string in quotes and you don't want the quotes, those would need to be stripped off. Since any cleanup will depend on the actual data and actual requirements, I won't complicate the formula farther with that.

Also, just like any function, the parameter values need to be legitimate. If an out-of-range index value is used, this will produce an error. So the formula can be wrapped with IFERROR to define what to do for the specific use case.

Note the limitation of this approach is that the list values cannot contain any embedded commas.