Loncat ke daftar isi utama

Bagaimana menemukan nilai dalam sel dengan daftar yang dipisahkan koma di Excel?

Misalkan Anda memiliki kolom yang berisi nilai yang dipisahkan koma seperti Sales, 123, AAA, dan sekarang Anda ingin mencari jika nilai 123 dalam sel yang dipisahkan koma, bagaimana Anda bisa melakukannya? Artikel ini akan memperkenalkan metode untuk memecahkan masalah tersebut.

Temukan nilai dalam sel dengan daftar dipisahkan koma dengan rumus

Temukan nilai dalam sel dengan daftar dipisahkan koma dengan rumus

Rumus berikut dapat membantu Anda menemukan nilai dalam sel dengan daftar yang dipisahkan koma di Excel. Silakan lakukan sebagai berikut.

1. Pilih sel kosong, masukkan rumus =IF(ISNUMBER(SEARCH(123,A2)),"yes","no") ke dalam Formula Bar dan kemudian tekan tombol Enter. Lihat tangkapan layar:

Note: dalam rumusnya, A2 adalah sel yang berisi nilai yang dipisahkan koma yang akan Anda temukan.

2. Tetap memilih sel hasil dan seret Fill Handle ke bawah untuk mendapatkan semua hasil. Jika nilai "123" ada di sel yang dipisahkan koma, Anda akan mendapatkan hasil sebagai "Ya"; jika tidak, Anda akan mendapatkan hasil sebagai "Tidak". Lihat tangkapan layar:

Artikel terkait:

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
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 Teratas:  12 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...

Deskripsi Produk

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 (17)
No ratings yet. Be the first to rate!
Iq
Iq
This comment was minimized by the moderator on the site
It’s working for single value, how do I find multiple values. Example: search values (101, 402,110) it might have all 3 or only one or two values and find cell will have any of three or all three or two.
Scenarios
1. Cell 1: 101 cell 2: 101,402,110,202,220
2. Cell 1: 101,402 cell 2: 101,402,202,220,110
3. Cell 1: 110,101,402 cell 2: 101,402,202,220,110

Pls advice
Reply
0
0
Hassam
Hassam
This comment was minimized by the moderator on the site
Hey, I still cant figure this out. I am still having the same problem. instead of using number in the formula I am using a cell reference. for example if i am searching for "1"(Q$1) in (123, 25,29,200)($J2) it is still giving me YES. The formula is =IF(ISNUMBER(SEARCH(Q$1,$J2)),"yes","no")

PS: I have a big data set I cannot put commas before and after in every cell. Will appreciate a solution
Reply
0
0
crystal
crystal     Hassam
This comment was minimized by the moderator on the site
Hi Hassam

Just use the following formula without the IF function. If the number matches exactly, you will get the result "TRUE", otherwise you will get the result "FALSE".
=ISNUMBER(FIND(",1,",","&$J2&","))
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/match.png
Reply
Report
0
0
Mohini Verma
Mohini Verma
This comment was minimized by the moderator on the site
Hi All, I have a cell that has multiple countries separated by commas
Example : A2 cell value is (India, Japan, Malaysia, Greater China)
Column G has the country names
Now , I need to find look for a formula that compares value from column G to value in A2 cell .
If the value matches, then return true otherwise return False
Reply
0
0
crystal
crystal     Mohini Verma
This comment was minimized by the moderator on the site
Hi Mohini Verma,
The formula can be modified as follows:
=IF(ISNUMBER(SEARCH(G2,A2)),"TRUE","FALSE")
Reply
Report
0
0
duncan
duncan
This comment was minimized by the moderator on the site
How about this please, I need to get the values in A and equate them with the figures in C and return the values in B separated as in commas respectively to C.

Thanks
View Attachments (1 / 5)
Reply
Report
0
0
crystal
crystal     duncan
This comment was minimized by the moderator on the site
Hi duncan,
If you are using Excel for 365, the following formula can help solving the problem.
=TEXTJOIN(",",TRUE,XLOOKUP(TRANSPOSE(VALUE(TEXTSPLIT(D1,","))),A1:A3,B1:B3,"",0,1))
This formula only works in Excel for 365. For other Excel versions other than Excel for 365, I have not found a formula that handles it..
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/find-value.png
Reply
Report
0
0
Aahad Memon
Aahad Memon
This comment was minimized by the moderator on the site
Hi Please help

I have table in Excel with two columns A & B, under A there are value Eg A2 Cell has 1,2,2,1,2,1,2 etc ( only has two numbers 1 and 2 but needs to recognise it has only 1 & 2 in the whole cell and no other number) and A3 has 1,1,1 and A4 has 0, A5 has 2,2,2,2 and A6 has 20. i want B2 should return the value for A2 as Asia, B3 Africa, B4 as None, B5 as America and B6 as Europe. I thought i could use the if formula but it is not able to recognise comma separated numbers and return value). Please Help
Reply
0
0
Scott S.
Scott S.
This comment was minimized by the moderator on the site
Hello!
This is a really cool formula, but it has ... flaw? ... that I cannot figure out how to work around it. "Flaw" is not the correct word, but I cannot think (it's 8:30 am on Monday) of the right word.
When you are looking for any part of a number, you get "yes". So, the following all return "yes":
=IF(ISNUMBER(SEARCH(12,A2)),"yes","no")
=IF(ISNUMBER(SEARCH(23,A2)),"yes","no")
=IF(ISNUMBER(SEARCH(3,A2)),"yes","no")

I tried wrapping the numbers in quotes to no avail. The formula works perfectly for the example's use case, in which all of the numbers are of the same length. But if you are looking for a shorter number that happens to appear in the string, you get a false positive.
Is there an alternative to "SEARCH" that is more literal (again, is that the word? haha)? 
Thanks! 
Reply
1
0
crystal
crystal     Scott S.
This comment was minimized by the moderator on the site
Hi Scott S.,Do you mean search for the exact string?
Reply
Report
0
0
Scott S.
Scott S.     crystal
This comment was minimized by the moderator on the site
Hi, Crystal.
Yes, I suppose that is the way to phrase it. 
The current solution will return “Yes” for any part of the number string. So the numbers 1, 2, 3, 12, 23, and 123 will all return “Yes”. 
I have been trying to come up with a solution that will search a string of numbers and return “Yes” for an exact match. 
Reply
0
0
crystal
crystal     Scott S.
This comment was minimized by the moderator on the site
Hi Scott S,Thanks for your feedback. I haven't found the solution yet and will get back to you if I found it.
Reply
Report
0
0
m.swartz55
m.swartz55     crystal
This comment was minimized by the moderator on the site
I have found a solution. If you add a comma to the left and right of your list it will require the search function to find an exact match from comma to comma. See example below:
=IF(ISNUMBER(SEARCH(",12,",A2)),"yes","no")

,312,123,10,112,126,
This will return false because there is no variable from comma to comma (,12,) that matches the search. 
If you need further clarification let me know and ill post a better example.I was having the same problem when automating a report for my boss and had an epiphany. :)
Reply
Report
0
0
Hassam
Hassam     m.swartz55
This comment was minimized by the moderator on the site
Hey, I still cant figure this out. I am still having the same problem. instead of using number in the formula I am using a cell reference. for example if i am searching for "1"(Q$1) in (123, 25,29,200)($J2) it is still giving me YES. The formula is =IF(ISNUMBER(SEARCH(Q$1,$J2)),"yes","no")

PS: I have a big data set I cannot put commas before and after in every cell. Will appreciate a solution
0
0
Mariah S.
Mariah S.     crystal
This comment was minimized by the moderator on the site
I have found a solution for this. If you add a comma to the left and right of your list and include them (like a sandwich) in your search it'll allow you to find an exact match. Example below:
,123,231,321,122,321,1,2,3,23,
SEARCH(",23,")
This way it forces the search to match from one comma to the other rather than just part of the entry from the list. Hope this is what you were looking for?
Reply
0
0
tapastkrtapan
tapastkrtapan
This comment was minimized by the moderator on the site
Good Morning to all.I have a excel question that how to count comma separated specific values in a single cell in excel.I want to count no of 15's in a single cell that contains 15,215,15,155,45,1515,15 I need the result 3. Is there any formula in excel.
Reply
Report
0
0
m.swartz55
m.swartz55     tapastkrtapan
This comment was minimized by the moderator on the site
Yes, there is a function in Excel that can do this for you. However, as explained in the problem above to gain better accuracy for your data add a comma to the left and right of your list and include it in your search. See example below:
,15,215,15,155,45,1515,15,
=COUNTIF(A1, ",15,")
The explanation of this formula is as follows: =COUNTIF(cell number or range, value your looking for/counting)
Let me know if you need a better example.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations