Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to select multiple items from drop down list into a cell in Excel?

The drop-down list is frequently used in the Excel daily work. By default, only one item can be selected in a drop-down list. But in some times, you may need to select multiple items from the drop down list into one single cell as below screenshot shown. How can you handle it in Excel?

doc select multiple items from drop down list 1 doc arrow right doc select multiple items from drop down list 2

Select multiple items from drop down list into a cell

Easily Combine columns or rows without losing data in Excel

Supposing you are working with a worksheet which contains multiple duplicate records, and now you need to combine / merge the rows based on the same value and do some calculations, such as sum, average, count of the duplicate rows. With this Advanced Combine Rows of Kutools for Excel, you can quickly combine same values / same data or duplicate rows into appropriate cells.  Click for 60 days free trial!
doc advanced combine rows
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

Select multiple items from drop down list into a cell


Here are some VBA can do you a favor on solving this job.

Select duplicate items from drop down list in a cell

1. After creating a drop-down list, right click at the sheet tab to select View Code from the context menu.
doc select multiple items from drop down list 3

2. Then in the Microsoft Visual Basic for Applications window, copy and paste below code to the blank script.

VBA: Select multiple items from drop down list in a cell

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20180510
    Dim xRgVal As Range
    Dim xStrNew As String
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    xStrNew = Target.Value
    Application.Undo
    xStrNew = xStrNew & " " & Target.Value
    Target.Value = xStrNew
    Application.EnableEvents = True
End Sub

doc select multiple items from drop down list 4

3. Save the code and close the window to go back to the drop down list. Now you can select multiple items from the drop down list.

Note:

1. With the VBA, it separates the items by space, you can change xStrNew = xStrNew & " " & Target.Value to others to change the delimiter as you need. For example, xStrNew = xStrNew & "," & Target.Value will separate the items with commas.

2. This VBA code works for all drop down lists in the sheet.

Select multiple items from drop down list to a cell without repeat

If you just want to select unique items from the drop down list to a cell, you can repeat above steps and use below code.

VBA:Select multiple items from drop down list to a cell without repeat

Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20180510
    Dim I As Integer
    Dim xRgVal As Range
    Dim xStrNew As String
    Dim xStrOld As String
    Dim xFlag As Boolean
    Dim xArr
    On Error Resume Next
    Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
    If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
    If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    xFlag = True
    xStrNew = " " & Target.Value & " "
    Application.Undo
    xStrOld = Target.Value
    If InStr(1, xStrOld, xStrNew) = 0 Then
        xStrNew = xStrNew & xStrOld & " "
    Else
        xStrNew = xStrOld
    End If
    Target.Value = xStrNew
    Application.EnableEvents = True
End Sub

 



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Madhu · 8 days ago
    I used this "Select multiple items from drop down list to a cell without repeat"
    this is what happened
    1. Multiple selections happen without problems.
    2. The issue is when i try to edit and remove an option. There is no way for me to remove an option. it keeps multiplying.
  • To post as a guest, your comment is unpublished.
    debadeep · 1 months ago
    After adding,the data from dropdown,delete doesnt work.
    • To post as a guest, your comment is unpublished.
      Susan Lynn · 1 months ago
      I found the same issue. I think I found a way to overcome it. At the bottom of the code you'll see the code "xStrNew = xStrOld". Instead, make it say xStrNew = " "

      It works for me.