Excel "UnCONCATENATE"/explode in function / Convert cell into array

Question

I am trying to "Unconcatenate" a string in Excel 2010. Yes, I know that is not a real word. So pretty much; I have a cell that can not be split into multiple columns the cell looks like this:

Item 1, Item 2, Item 3

Now this cell may have 0-? items. I am wanting to compare that one cell against a column in another sheet. I believe I would need to use the match function to do this, but I need that first cell to be converted into an array in a function with the delimiter as the comma. So far I have =MATCH(Each item in cell, SHEET2!A:A, 0)

Any help would be nice. I am aware of =Left and =Right, but I do not think these will work because the number of items in each cell may not be the same. Thanks

Edit:

Detailed discription: In my first sheet I have a dropdown box. When you choose items it does a vlookup on sheet 2 on this item. When this happens I want it to also check if cell E in that row (item 1, item 2, item 3) match any of the individual cells in a column in sheet 3

Solution

The following code exposes VBA's Split function for worksheet use--it returns a row array of items that have been split using a specified delimiter. For example, if cell A1 contained the text "Item 1,Item 2"), EXPLODE(A1,",") would return an array with elements "Item 1" and "Item 2".

  Function EXPLODE(str As String, Optional delimiter As Variant) As Variant
      If IsMissing(delimiter) Then
          delimiter = " "
      End If
      EXPLODE = Split(str, delimiter)
  End Function 

It is an array function. To use the returned elements in the spreadsheet:

• Select the cells in which you want the "exploded" items show
• Enter the function specifying the cell with the source string (or reference to the cell which contains the source) and the delimiter on which the split will be done
• Complete the entry using the Control-Shift-Enter key combination.

Alternatively, individual elements can be chosen using the INDEX function--=INDEX(EXPLODE(A1,1,2) would return "Item 2" using the previous example. (Given a range or array, the INDEX function returns the value in the ith row and jth column.) This usage does not require the formula to be entered as an array formula.

For your use case, a combination with other functions would be in order. You have a string with multiple items of the form "aa, bb, cc" (the result of a VLOOKUP) and want to determine whether any of the elements of this string can be found as individual items in any of the cells in column A. You want a function that will return True if all of the elements are found, and False otherwise. The following formula achieves that result:

  =SUM(SIGN(IFERROR(MATCH(TRIM(EXPLODE(D1,",")),$A:$A,0),0)))=COUNTA(EXPLODE(D1,","))

It is an array formula and needs to be entered with Control-Shift-Enter. Note that I used the contents of cell D1 in lieu of your lookup value. The TRIM function strips out any extraneous spaces between the elements of the string with multiple items.

Comments

[James Ray]

Correct. And I would like it to be Boolean, true if all match, else false.

[James Ray]

When I try this, it comes up with #Name error. I have tried adding as an array function and as a regular function.

[chuff]

You may not have macros enabled. From the ribbon, go to File / Options / Trust Center and then press the Trust Center Settings button. You need to make sure that Enable All Macros is selected and Trust access to the VBA project model is checked.

[James Ray]

it was not enabled... here is my code =IF(($C9=""),"",IF(MATCH(EXPLODE(VLOOKUP($C9,'Equipment Lookup'!$A:$H,5,FALSE), ", "), Sheet2!A:A, 0), "True", "False"))

[James Ray]

Compile error: Invalid Next control variable reference The Next ListItem was highlighted when populating the dictionary.

[James Ray]

Got this one to work by adding it to a new module... however it is only giving me item 1 in my function... i need a way of checking all items... I am seeing some progress with NickSlash, any advice you can help us with that?

[chuff]

Try =SUM(IFERROR(MATCH(EXPLODE(VLOOKUP($C9,'Equipment Lookup'!$A:$H,5,FALSE),","),Sheet2!A:A,0),0))<>0 (entered as array formula). I tested this against a string of the form "a,b,c" or "1,2,3", but could not validate against what the VLOOKUP returns.

[NickSlash]

I've updated the code if you haven't managed to figured out how to fix it already.

[James Ray]

I was unable to get this one to work.. Thank you for your help. But I got Chuff's to work. When i used this I got a #VALUE error this time.