Loncat ke daftar isi utama

Bagaimana cara mengembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma?

Di Excel, kita dapat menerapkan fungsi VLOOKUP untuk mengembalikan nilai pertama yang cocok dari sel tabel, tetapi, terkadang, kita perlu mengekstrak semua nilai yang cocok dan kemudian dipisahkan oleh pemisah tertentu, seperti koma, tanda hubung, dll… menjadi satu sel seperti gambar berikut yang ditampilkan. Bagaimana kita bisa mendapatkan dan mengembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma di Excel?

doc mengembalikan beberapa nilai yang dipisahkan koma 1

Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Fungsi Buatan Pengguna

Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Kutools for Excel


Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Fungsi Buatan Pengguna

Biasanya, tidak ada cara langsung bagi kami untuk mengekstrak dan mengembalikan beberapa nilai yang cocok dan dipisahkan dengan koma ke dalam satu sel, di sini, Anda dapat membuat Fungsi Buatan Pengguna untuk menyelesaikan pekerjaan ini, lakukan hal berikut:

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di Modul Jendela.

Kode VBA: Kembalikan beberapa nilai pencarian ke dalam satu sel yang dipisahkan koma

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. Kemudian simpan kode ini dan tutup jendela Modul, kembali ke lembar kerja Anda, dan masukkan rumus ini: = SingleCellExtract (D2, A2: B15,2, ",") ke dalam sel kosong yang ingin Anda kembalikan hasilnya. Lalu tekan Enter kunci untuk mendapatkan hasilnya, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 2

Note: Dalam rumus di atas:

D2: menunjukkan nilai sel yang ingin Anda cari;

A2: B15: adalah rentang data yang Anda inginkan untuk mengambil data;

2: angka 2 adalah nomor kolom yang nilai pencocokannya akan dikembalikan;

,: koma adalah pemisah yang ingin Anda pisahkan beberapa nilai.

Anda dapat mengubahnya sesuai kebutuhan Anda.


Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Kutools for Excel

Jika Anda memiliki Kutools untuk Excel, tugas ini tidak lagi menjadi masalah. Itu Lanjutan Gabungkan Baris utilitas dapat membantu Anda menggabungkan semua nilai relatif berdasarkan kolom.

Kutools untuk Excel : dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari

Setelah menginstal Kutools untuk Excel, lakukan hal berikut:

1. Pilih rentang data yang ingin Anda gabungkan semua nilai yang cocok berdasarkan kolom.

2. Klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris, lihat tangkapan layar:

3. di Gabungkan Baris Berdasarkan Kolom kotak dialog, klik nama kolom yang ingin Anda gabungkan berdasarkan, lalu klik Kunci utama tombol, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 4

4. Lalu klik nama kolom lain yang ingin Anda gabungkan nilainya yang cocok, dan klik Menggabungkan untuk memilih satu pemisah untuk memisahkan nilai gabungan, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 5

5. Lalu klik OK tombol, semua sel yang sesuai dengan nilai yang sama telah digabungkan menjadi satu sel yang dipisahkan dengan koma, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 6 2 doc mengembalikan beberapa nilai yang dipisahkan koma 7

Klik untuk mengetahui detail selengkapnya tentang utilitas Advanced Combine Rows ini…

Unduh dan uji coba gratis Kutools untuk Excel Sekarang!


Demo: Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Kutools for Excel

Kutools untuk Excel: dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari. Unduh dan uji coba gratis Sekarang!

Alat Produktivitas Kantor Terbaik

Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

tab kte 201905


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
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