1

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.
  • As far as I could tell, I think you'd need to search out all the delimiters and mid them. – Raystafarian Oct 23 '14 at 16:42
  • Yeah, I looked at that and you beat me to the approach you posted. I was toying with an approach using CHOOSE and couldn't see a way to do it. – fixer1234 Oct 23 '14 at 16:46
  • It would have been too difficult as the list wasn't always the same number of items.. – Raystafarian Oct 23 '14 at 16:50
  • That was the easy part. I was just going to build an expression with 29 terms (the max for CHOOSE), and use IFERRERs to turn unused items to null. – fixer1234 Oct 23 '14 at 16:57
  • It seems like what you want is the SPLIT function (exists in VBA but is not accessible from the worksheet), but use it with INDEX rather than CHOOSE. For example, =INDEX(SPLIT(B1,","),1). – Excellll Oct 23 '14 at 20:16
2

I would like to suggest a comparatively easy method.

enter image description here

Column A holds the string of values to be selected. Column B holds the index value for the choice. Column C shows the selected value.

The formula

  • Put Occurrence value in Column B.
  • Write this Formula in Cell C2 if the values are delimited with a space:

    =TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))),(B2-1)*LEN(A$2)+1,LEN(A$2)))
    
  • You can use this Formula in Cell C9 for Comma separated Values.

    =TRIM(MID(SUBSTITUTE(A$9,",",REPT(" ",LEN(A$9)),(B9-1)*LEN(A$9)+1,LEN(A$9)))
    

How it works

This method replaces each delimiter with spaces equal to the length of the entire data string. Then it calculates break points that start at or before each value, and snips a segment as long as the entire original string. The snippet will always contain the target value plus some padding, and no part of any other value. The padding is trimmed and you are left with the target value.

Let me break the Formula:

When in Helper Column Cell B2 has 3, we're looking for the third value, Data3.

LEN(A2) returns:  23

SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2)))  Returns: 

Data1                       Data2                       Data3                       Data4 

Then:

(B2-1)*LEN(A2)+1 returns:  47  

This is a position between Data2 and Data3, and 23 characters later is a position after Data3. The MID function gets that snippet, containing padding, Data3, and more padding.

TRIM removes the padding, leaving `Data3`.

Note, If Helper Col Value is 2 then Formula

=(B2-1)*LEN(A2)+1

returns 24 instead of 47

and the Formula

=TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))),24,23))

returns Data2.

N.B. Adjust cell references in the Formula as needed.

  • @fixer1234,, sure soon I'll ☺ – Rajesh S Feb 2 '19 at 10:48
  • @fixer1234,, now check the post, I've explained how the Formula gets desire value using helper Column. ☺ – Rajesh S Feb 2 '19 at 12:11
  • @Fixer1234, thanks for useful add on to the post makes it better n better. ☺ – Rajesh S Feb 3 '19 at 7:22
1

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))

enter image description here

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.

1

When it comes to string manipulation I always like to use regular expressions.

I've first found Excel RegEx Find / Replace add-in, so use that, but you probably can find other free tools too.

With that simplest way would be to use this formula:
=RegExReplace($A$1,"([^,]*,){"&C1-1&"}([^,]*)(,.*)?","$2")

enter image description here

How it works:

  • =RegExReplace($A$1,"([^,]*,){"&C1-1&"}([^,]*)(,.*)?","$2")
  • ([^,]*,){"&C1-1&"}
    • ([^,]*,) - matches a character sequence not containing comma (,) then a single comma following them
      • (also remembers it as group #1)
    • {"&C1-1&"} - previous group C1 minus one times
    • ([^,]*) - matches a character sequence not containing comma, also remembers it as group #2
    • (,.*)? - matches a comma followed by any sequence of characters. (?: this part is optional, might not exist in case the last item is required).
    • "$2" - replaces original string with group stored as #2

Read more explanation here.

If input is a valid index, the right item will be the return, for non-valid index (0 or higher than number of items) the whole input string will be the result.

This formula works for your example, but it's not working for all "CSV conform lists" as in your question. (neither other answers to your questions as I saw, however this solution can be easier improved if you need to do so):

  • as the formula in my original answer, it doesn't remove quotes (") around entries, however it's easy to solve with another function around original:
    =RegExReplace(...,"""(.*)""","$1")
  • more difficult part is to manage delimiters within items ("first","second,","third") and escaped delimiters ("quote "" within item")
  • Thanks for posting this. I've been using LO Calc for some years, which was why I wanted to avoid a VBA solution. I did a quick search and couldn't find a similar add-in for it. Calc can do some regex in some functions, but so far, I haven't seen a way to adapt this. Still, it looks like a solution that would work for others, and you've demonstrated that it works in the example. For people who use little or no regex (including me), can you save some endangered brain cells and add a few sentences explaining how it works? With that, I'll happily upvote it. :-) – fixer1234 Feb 3 '19 at 21:02
  • BTW, does your reference to not working with CSV conforming lists refer to not stripping the quotes from quoted text or something else? – fixer1234 Feb 3 '19 at 21:03
  • 1
    Thanks for the feedback, please see my update. – Máté Juhász Feb 4 '19 at 8:49
  • According to LO help, SEARCH function supports regular expressiions. Can you please confirm, so I could provide a solution using that. (e.g. what's the output of =SEARCH("[0-9]+","abcd1234efg") – Máté Juhász Feb 4 '19 at 8:54
  • Did that return 5?? – Máté Juhász Feb 4 '19 at 10:04

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.