Přejít k hlavnímu obsahu

Jak vrátit více hodnot vyhledávání v jedné buňce oddělené čárkami?

V aplikaci Excel můžeme použít funkci VLOOKUP k vrácení první shodné hodnoty z buněk tabulky, ale někdy musíme extrahovat všechny odpovídající hodnoty a poté je oddělit konkrétním oddělovačem, například čárkou, pomlčkou atd ... do jednoho buňka jako následující snímek obrazovky. Jak bychom mohli získat a vrátit více hodnot vyhledávání v jedné buňce oddělené čárkami v aplikaci Excel?

doc vrací více hodnot oddělených čárkami 1

Vraťte více hodnot vyhledávání v jedné buňce oddělené čárkami pomocí funkce definované uživatelem

Vraťte více hodnot vyhledávání v jedné buňce oddělené čárkami pomocí programu Kutools pro Excel


Vraťte více hodnot vyhledávání v jedné buňce oddělené čárkami pomocí funkce definované uživatelem

Normálně pro nás neexistuje žádný přímý způsob, jak extrahovat a vrátit více hodnot shody a oddělit je čárkou do jedné buňky, zde můžete vytvořit funkci definovanou uživatelem k vyřešení této úlohy, postupujte takto:

1. Podržte ALT + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do Modul Okno.

Kód VBA: Vraťte více hodnot vyhledávání do jedné buňky oddělené čárkami

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. Poté uložte tento kód a zavřete okno modulu, vraťte se do svého listu a zadejte tento vzorec: = SingleCellExtract (D2, A2: B15,2, ",") do prázdné buňky, které chcete vrátit výsledek. A pak stiskněte vstoupit klíč k získání výsledku, viz screenshot:

doc vrací více hodnot oddělených čárkami 2

Poznámka: Ve výše uvedeném vzorci:

D2: označuje hodnoty buněk, které chcete vyhledat;

A2: B15: je rozsah dat, který chcete načíst data;

2: číslo 2 je číslo sloupce, který má vrátit odpovídající hodnotu;

,: čárka je oddělovač, kterým chcete oddělit více hodnot.

Můžete je podle potřeby změnit.


Vraťte více hodnot vyhledávání v jedné buňce oddělené čárkami pomocí programu Kutools pro Excel

Pokud máte Kutools pro Excel, tento úkol již nebude problémem. The Pokročilé kombinování řádků nástroj vám může pomoci kombinovat všechny relativní hodnoty založené na sloupci.

Kutools pro Excel : s více než 300 praktickými doplňky Excel, můžete si je vyzkoušet bez omezení do 30 dnů

Po instalaci Kutools pro Excel, postupujte následovně:

1. Vyberte rozsah dat, který chcete kombinovat všechny odpovídající hodnoty na základě sloupce.

2, klikněte Kutools > Sloučit a rozdělit > Pokročilé kombinování řádků, viz screenshot:

3. V Kombinujte řádky na základě sloupce V dialogovém okně klikněte na název sloupce, podle kterého chcete kombinovat, a poté klikněte na Primární klíč tlačítko, viz screenshot:

doc vrací více hodnot oddělených čárkami 4

4. Poté klikněte na název jiného sloupce, u kterého chcete zkombinovat odpovídající hodnoty, a klikněte na Kombinovat vyberte jeden oddělovač k oddělení kombinovaných hodnot, viz screenshot:

doc vrací více hodnot oddělených čárkami 5

5. Pak klikněte na tlačítko OK tlačítko, všechny odpovídající buňky se stejnou hodnotou byly sloučeny do jedné buňky, které jsou odděleny čárkou, viz screenshoty:

doc vrací více hodnot oddělených čárkami 6 2 doc vrací více hodnot oddělených čárkami 7

Kliknutím zobrazíte další podrobnosti o tomto nástroji Advanced Combine Rows…

Stáhněte si zdarma zkušební verzi Kutools pro Excel!


Ukázka: Vraťte více hodnot vyhledávání v jedné buňce oddělené čárkou pomocí programu Kutools pro Excel

Kutools pro Excel: s více než 300 praktickými doplňky aplikace Excel, můžete vyzkoušet bez omezení do 30 dnů. Stáhněte si a vyzkoušejte zdarma hned teď!

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Comments (16)
Rated 5 out of 5 · 2 ratings
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
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!
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.
Rated 4.5 out of 5
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!
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!
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
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.
Rated 5 out of 5
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?
Rated 5 out of 5
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.......
This comment was minimized by the moderator on the site
Error pops up if increase the array size
This comment was minimized by the moderator on the site
VB command breaks when the range is longer than 154 rows (ie :B154)....
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?
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
This comment was minimized by the moderator on the site
This works but slows down my excel majorly! Any tips to help speed?
This comment was minimized by the moderator on the site
Thanks for this post. Do you know how I would go about manipulating the two separate integers this is creating. For example, lets say that the '=SingleCellExtract' function now produces (1 , 2). Is there a way to have a cell next to it that does (1+.5 , 2+.5)?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations