Salt la conținutul principal

Cum se returnează mai multe valori de căutare într-o celulă separată prin virgule?

În Excel, putem aplica funcția VLOOKUP pentru a returna prima valoare potrivită dintr-o celulă de tabel, dar, uneori, trebuie să extragem toate valorile potrivite și apoi să le separăm printr-un delimitator specific, cum ar fi virgulă, liniuță etc. într-o singură celulă după cum se arată în următoarea captură de ecran. Cum am putea obține și returna mai multe valori de căutare într-o singură celulă separată prin virgule în Excel?

doc returnează mai multe valori separate prin virgulă 1

Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Funcția definită de utilizator

Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Kutools pentru Excel

Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Funcția definită de utilizator

În mod normal, nu există o modalitate directă de a extrage și a returna valorile de potrivire multiple și separate prin virgulă într-o singură celulă, aici puteți crea o funcție definită de utilizator pentru a rezolva această sarcină, vă rugăm să faceți următoarele:

1. Țineți apăsat butonul ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Moduleși lipiți următorul cod în Module Fereastră.

Cod VBA: returnează mai multe valori de căutare într-o singură celulă separată prin virgulă

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. Apoi salvați acest cod și închideți fereastra Module, reveniți la foaia de lucru și introduceți această formulă: = SingleCellExtract (D2, A2: B15,2, ",") într-o celulă goală în care doriți să returnați rezultatul. Și apoi apăsați Intrați cheie pentru a obține rezultatul, vezi captura de ecran:

doc returnează mai multe valori separate prin virgulă 2

notițe: În formula de mai sus:

D2: indică valorile celulei pe care doriți să le căutați;

A2: B15: este intervalul de date pe care doriți să îl preluați;

2: numărul 2 este numărul coloanei care trebuie returnată valoarea potrivită;

,: virgula este separatorul pe care doriți să separați valorile multiple.

Le puteți schimba după nevoile dvs.

Returnează mai multe valori de căutare într-o celulă separată prin virgulă cu Kutools pentru Excel

Dacă aveţi Kutools pentru Excel, această sarcină nu va mai fi o problemă. Rânduri combinate avansate utilitarul vă poate ajuta să combinați toate valorile relative pe baza unei coloane.

Kutools pentru Excel : cu mai mult de 300 de programe de completare Excel la îndemână, gratuit pentru a încerca fără limitări în 30 de zile. 
Accesați Descărcare
Încercare gratuită 30 de zileCumpărare
PayPal / MyCommerce

După instalare Kutools pentru Excel, vă rugăm să faceți următoarele:

1. Selectați intervalul de date pe care doriți să combinați toate valorile potrivite pe baza unei coloane.

2. Clic Kutools > Merge & Split > Rânduri combinate avansate, vezi captura de ecran:

3. În Combinați rândurile pe baza coloanei caseta de dialog, faceți clic pe numele coloanei pe care doriți să o combinați, apoi faceți clic pe Cheia principala buton, vezi captura de ecran:

doc returnează mai multe valori separate prin virgulă 4

4. Apoi faceți clic pe numele altei coloane pe care doriți să le combinați valorile potrivite și faceți clic pe Combina pentru a alege un separator pentru a separa valorile combinate, consultați captura de ecran:

doc returnează mai multe valori separate prin virgulă 5

5. Apoi apasa OK buton, toate celulele corespunzătoare cu aceeași valoare au fost combinate într-o singură celulă care este separată cu virgula, a se vedea capturile de ecran:

doc returnează mai multe valori separate prin virgulă 6 2 doc returnează mai multe valori separate prin virgulă 7

Faceți clic pentru a afla mai multe detalii despre acest utilitar Advanced Combine Rows ...

Descărcați și proba gratuită Kutools pentru Excel acum!

Demo: Returnează mai multe valori de căutare într-o celulă separată prin virgule cu Kutools pentru Excel

Kutools pentru Excel: cu mai mult de 300 de programe de completare Excel la îndemână, încercați fără limitări în 30 de zile. Descărcați și proba gratuită acum!

Cele mai bune instrumente de productivitate de birou

🤖 Kutools AI Aide: Revoluționați analiza datelor pe baza: Execuție inteligentă   |  Generați codul  |  Creați formule personalizate  |  Analizați datele și generați diagrame  |  Invocați funcțiile Kutools...
Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii  |   VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar   |  Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule   |  Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente:  12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp.  Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...

Descriere

Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
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