Direkt zum Inhalt

Wie kann ich mehrere Suchwerte in einer durch Kommas getrennten Zelle zurückgeben?

In Excel können wir die VLOOKUP-Funktion anwenden, um den ersten übereinstimmenden Wert aus einer Tabellenzelle zurückzugeben. Manchmal müssen wir jedoch alle übereinstimmenden Werte extrahieren und dann durch ein bestimmtes Trennzeichen wie Komma, Bindestrich usw. in ein einzelnes trennen Zelle wie im folgenden Screenshot gezeigt. Wie können wir in Excel mehrere Suchwerte in einer durch Kommas getrennten Zelle abrufen und zurückgeben?

doc gibt mehrere durch Kommas getrennte Werte zurück 1

Geben Sie mit der benutzerdefinierten Funktion mehrere Suchwerte in einer durch Kommas getrennten Zelle zurück

Geben Sie mit Kutools for Excel mehrere Suchwerte in einer durch Kommas getrennten Zelle zurück

Geben Sie mit der benutzerdefinierten Funktion mehrere Suchwerte in einer durch Kommas getrennten Zelle zurück

Normalerweise gibt es für uns keine direkte Möglichkeit, die mehreren übereinstimmenden Werte, die durch Komma getrennt sind, in eine Zelle zu extrahieren und zurückzugeben. Hier können Sie eine benutzerdefinierte Funktion erstellen, um diesen Job zu lösen. Gehen Sie dazu wie folgt vor:

1. Halten Sie die Taste gedrückt ALT + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modul Fenster.

VBA-Code: Geben Sie mehrere Suchwerte in eine durch Kommas getrennte Zelle zurück

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. Speichern Sie dann diesen Code und schließen Sie das Modulfenster, kehren Sie zu Ihrem Arbeitsblatt zurück und geben Sie die folgende Formel ein: = SingleCellExtract (D2, A2: B15,2, ",") in eine leere Zelle, in der Sie das Ergebnis zurückgeben möchten. Und dann drücken Enter Schlüssel, um das Ergebnis zu erhalten, siehe Screenshot:

doc gibt mehrere durch Kommas getrennte Werte zurück 2

Note: In der obigen Formel:

D2: gibt die Zellenwerte an, nach denen Sie suchen möchten;

A2: B15: ist der Datenbereich, in den Sie die Daten abrufen möchten;

2: Die Nummer 2 ist die Spaltennummer, deren übereinstimmender Wert zurückgegeben werden soll.

,: Das Komma ist das Trennzeichen, durch das Sie die mehreren Werte trennen möchten.

Sie können sie nach Ihren Wünschen ändern.

Geben Sie mit Kutools for Excel mehrere Suchwerte in einer durch Kommas getrennten Zelle zurück

Wenn Sie Kutools for Excelwird diese Aufgabe kein Problem mehr sein. Das Erweiterte Zeilen kombinieren Das Dienstprogramm kann Ihnen dabei helfen, alle relativen Werte basierend auf einer Spalte zu kombinieren.

Kutools for Excel : Mit mehr als 300 praktischen Excel-Add-Ins können Sie diese innerhalb von 30 Tagen ohne Einschränkung testen. 
Gehe zu Download
Kostenlose Testversion 30 TageBezug von MarvelClient
PayPal / MyCommerce

Nach der Installation Kutools for ExcelBitte gehen Sie wie folgt vor:

1. Wählen Sie den Datenbereich aus, in dem Sie alle übereinstimmenden Werte basierend auf einer Spalte kombinieren möchten.

2. Klicken Sie Kutoolen > Zusammenführen & Teilen > Erweiterte Zeilen kombinieren, siehe Screenshot:

3. In dem Zeilen basierend auf Spalte kombinieren Klicken Sie im Dialogfeld auf den Spaltennamen, auf dessen Grundlage Sie kombinieren möchten, und klicken Sie dann auf Primärschlüssel Schaltfläche, siehe Screenshot:

doc gibt mehrere durch Kommas getrennte Werte zurück 4

4. Klicken Sie dann auf einen anderen Spaltennamen, dessen übereinstimmende Werte kombiniert werden sollen, und klicken Sie auf Kombinieren Um ein Trennzeichen zum Trennen der kombinierten Werte auszuwählen, siehe Screenshot:

doc gibt mehrere durch Kommas getrennte Werte zurück 5

5. Dann klick OK Schaltfläche, alle entsprechenden Zellen mit dem gleichen Wert wurden zu einer Zelle zusammengefasst, die durch Komma getrennt sind, siehe Screenshots:

doc gibt mehrere durch Kommas getrennte Werte zurück 6 2 doc gibt mehrere durch Kommas getrennte Werte zurück 7

Klicken Sie hier, um weitere Informationen zu diesem Dienstprogramm für erweiterte Kombinationszeilen zu erhalten.

Laden Sie Kutools for Excel jetzt herunter und testen Sie es kostenlos!

Demo: Geben Sie mit Kutools for Excel mehrere Suchwerte in einer durch Kommas getrennten Zelle zurück

Kutools for Excel: Mit mehr als 300 praktischen Excel-Add-Ins können Sie es innerhalb von 30 Tagen ohne Einschränkung testen. Jetzt herunterladen und kostenlos testen!

Beste Office-Produktivitätstools

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate   |  Leere Zeilen löschen   |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren   |   Runde ohne Formel ...
Super-Lookup: VLookup mit mehreren Kriterien  |   VLookup mit mehreren Werten  |   VLookup über mehrere Blätter hinweg   |   Unscharfe Suche ....
Erweiterte Dropdown-Liste: Erstellen Sie schnell eine Dropdown-Liste   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl Dropdown-Liste ....
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Vergleichen Sie Bereiche und Spalten ...
Ausgewählte Funktionen: Rasterfokus   |  Designansicht   |   Große Formelleiste   |  Arbeitsmappen- und Blattmanager   |  Ressourcen (Autotext)   |  Datumsauswahl   |  Arbeitsblätter kombinieren   |  Zellen verschlüsseln/entschlüsseln   |  Senden Sie E-Mails nach Liste   |  Superfilter   |   Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets:  12 Text Tools (Text hinzufügen, Zeichen entfernen, ...)   |   50+ Chart Typen (Gantt-Diagramm, ...)   |   40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag, ...)   |   19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen, ...)   |   12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion, ...)   |   7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Zellen teilen, ...)   |   ... und mehr

Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen.  Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...

Beschreibung

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
Comments (19)
Rated 5 out of 5 · 2 ratings
Bruno
Bruno
This comment was minimized by the moderator on the site
Como faço para ajustar a função do "MultipleValues" para ignorar células vazias?

Pois no meu caso tenho algumas células em branco e quando faço o merge, fico com algumas vírgulas repetidas.

Ex.: (001,002,,,,003).

Obrigado!
Reply
0
0
skyyang
skyyang     Bruno
This comment was minimized by the moderator on the site
Hello, Bruno,
To ignore the blank cells when combine, pleae apply the following code:

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String) As String
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            ' Check if the cell in the specified column is not empty before appending
            If Not IsEmpty(LookupRange.Cells(I, ColumnNumber)) Then
                If xRet = "" Then
                    xRet = LookupRange.Cells(I, ColumnNumber) & Char
                Else
                    xRet = xRet & LookupRange.Cells(I, ColumnNumber) & Char
                End If
            End If
        End If
    Next
    If Len(xRet) > 0 Then
        SingleCellExtract = Left(xRet, Len(xRet) - 1)
    Else
        SingleCellExtract = ""
    End If
End Function


Please have a try, hope it can help you!
Reply
Report
0
0
Bruno
Bruno     skyyang
This comment was minimized by the moderator on the site
Hello skyyang,

Yes, it works!

Thank you!
Reply
0
0
Yery
Yery
This comment was minimized by the moderator on the site
No soy programador pero necesito ayuda para que la función de abajo en lugar de devolverme esto

S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4;S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

me devuelva los valores en líneas diferentes.
S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4
S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

La función es:
Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
Dim I As Long
Dim xRet As String
For I = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(I, 1) = LookupValue Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function
Reply
0
0
skyyang
skyyang     Yery
This comment was minimized by the moderator on the site
Hello, Yery,
Did you mean to split a cell into multiple rows based on the semicolon character?
If so, the following VBA code can help you:
Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Kutools for Excel"
            Exit Sub
            End If
            Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")
            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False
                For Each xCell In xRg
                    xRet = Split(xCell.Value, ";")
                    xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    I = I + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

Please have a try, hope it can help you!
Reply
Report
0
0
Manikanta
Manikanta
This comment was minimized by the moderator on the site
Hi,

I want exact validation code for multiple values separated by comma and space for each value.

Example:
Lucy, Tom, Nicol, Akash, Apple

Please replay if you have any suggestions.
Reply
0
0
Rated 4.5 out of 5
skyyang
skyyang     Manikanta
This comment was minimized by the moderator on the site
Hello， Manikanta
To separate the multiple values by a comma and space, you just need to add a space behind the comma, change the formula as this: =SingleCellExtract(D2,A2:B15,2,", ").
Please try, hope it can help you!
Reply
Report
0
0
Manikanta
Manikanta     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

Thanks for your replay!

I tried already same way but in the cell value last one extra comma (,) coming below is the example.

Lucy, Tom, Nicol, Akash, Apple,

This will not work for Json file, so I want values separated by comma and space like below.

Lucy, Tom, Nicol, Akash, Apple

Thank you!
Reply
0
0
skyyang
skyyang     Manikanta
This comment was minimized by the moderator on the site
Hello, Manikanta
In this case, you can apply the below User Defined Function:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function


After pasting the code, please use this formula: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Please try, hope this can help you!
If you still have any other problem, please comment here.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
Reply
Report
0
0
Manikanta
Manikanta     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

This is working now, Thank you for your quick response.

It's very use full to me once again Thank you for your help.

Regards,
Manikanta.
Reply
0
0
Rated 5 out of 5
KPRS92
KPRS92
This comment was minimized by the moderator on the site
Good Morning,

the VBA code worked perfectly with my worksheet, pretty clear and simple, however, I was trying to find a way to tell excel to only return the unique values. Would that be possible using this same code?
Reply
Report
0
0
Rated 5 out of 5
Biplab Das
Biplab Das
This comment was minimized by the moderator on the site
When 2 Criteria Match then Return Multiple Lookup Values In One Comma Separated Cell
A2=B2 Then Result From Range by "SingleCellExtract" - Please.......
Reply
0
0
abhi
abhi
This comment was minimized by the moderator on the site
Error pops up if increase the array size
Reply
0
0
Js
Js
This comment was minimized by the moderator on the site
VB command breaks when the range is longer than 154 rows (ie :B154)....
Reply
0
0
M Macklam
M Macklam
This comment was minimized by the moderator on the site
thank you, firstly i managed to get this to work without the slow down in performance. I'm using values rather that text so my question is i want to bring back all those with less than say 19 points in a list. Can the single cell Extract work for that or does it have to be a specific value?
Reply
0
0
A Ward
A Ward
This comment was minimized by the moderator on the site
This simply does not work. I was unable to get it to work in my own application, so I copy/pasted the vba and the formula and it returned an error every time
Reply
0
0
K Ray
K Ray
This comment was minimized by the moderator on the site
This works but slows down my excel majorly! Any tips to help speed?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations