0

Im trying for some hours already to split a text cell into a usable object to the validation list.

The cells today are populated by 3 or more (N) comma (I can change that to semicolons, pipes...) separated values like "1,2,3" or "5,test,8,new"

I want to transform that into a validation list with N different values.

In the following post there is a formula that splits a string into and array, but I have not managed to use it as source: Split a string (cell) in Excel without VBA (e.g. for array formula)

Is it possible to create a Dynamic Range from splitting the cell text and feeding it into a 'combobox' (validation list).

Another similar example of what I am looking for but without VBA: Excel Dynamic Drop Down Using Comma Delimited Data

What am I doing wrong here? Thanks for reading this far.

BBacon
  • 2,456
  • 5
  • 32
  • 52
  • 1
    AFAIK you'll actually have to put the values in separate cells. Using a formula in a validation won't result in three different selectable values. – BruceWayne Jul 15 '17 at 20:40

2 Answers2

1

It is possible to formulate this using Find. To split the text in cell "A1", the formula will be:

  • 1st part - cell B1 formula:
    =LEFT(A1,FIND(",",A1)-1)
  • 2nd part - cell C1 formula:
    =MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1)
  • 3rd part - cell D1 formula:
    =MID(A1,FIND(",",A1,FIND(",",A1)+1)+1,999999)

Then a list validation may be added to Cell E1, with the range B1:D1 enter image description here

curious
  • 1,504
  • 5
  • 18
  • 32
  • 1
    It's not possible to do it without populating this new columns with the formula? I can't obtain the list directly? – BBacon Jul 16 '17 at 15:01
  • 2
    hi MBarni, thats right, the list can't be obtained directly as excel data validation accepts either a list or a formula - but not both – curious Jul 16 '17 at 17:19
1

You can do this by

  • Use a formula to break up the comma separated list into separate cells
  • Then use dynamic range names to refer to the DV List

Formula

to return an array in the sequence of {1,99,198,297, ...}

Create a defined name formula: (Formulas --> define name)

seq_99   Refers To: =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

With your lists in row 1 on some worksheet, use the following formula:

A2: =INDEX(TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",99)),seq_99,99)),ROWS($1:1))

and fill down as far as needed to account for your longest list. I assumed A2:A10 would be sufficient.

enter image description here

Next, create dynamic named formulas to refer to just the portion of each list that contains data.

Example:

dvList1 Refers to:  =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A$1:$A$10)-COUNTBLANK(Sheet2!$A$1:$A$10)-1)

Format your DataValidation cell as a List with the named formula:

enter image description here

The list will now show the non-blank entries.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60