Anda disini: M Jurnal » Rumus Excel » Rumus CONCATENATE: 11+ Cara Menggabungkan Data Excel

Rumus CONCATENATE: 11+ Cara Menggabungkan Data Excel

Rumus / Fungsi CONCATENATE Excel adalah sebuah formula yang berguna untuk menggabungkan 2 (atau lebih) data, baik ketik secara manual, sebuah cell, kolom, baris ataupun data hasil sebuah rumus menjadi 1 cell.

Note: Perlu Anda ketahui, menggabungkan data menggunakan Rumus CONCATENATE bukan berarti Anda bisa menggabungkan 2 cell atau lebih menjadi 1.

Merge and Center Excel

Untuk keperluan menggabungkan cell seperti gambar di atas, silahkan kunjungi Panduan Merge and Center. Selain untuk keperluan tersebut, Anda bisa mengikuti Panduan ini.

Oh ya, Panduan kali ini cukup panjang karena Saya bahas dari contoh paling dasar hingga contoh paling kompleks. Jangan lupa sediakan kopi Anda…

Aturan Penulisan Rumus CONCATENATE Excel

Sama seperti Rumus Excel lainnya, Fungsi CONCATENATE juga memiliki aturan penulisan yang wajib Anda pahami. Aturan ini sudah menjadi Syarat mutlak agar Excel tidak memberikan pesan error. Berikut aturan penulisan Rumus CONCATENATE:

Tulis Rumus / Fungsi CONCATENATE:
=
CONCATENATE(text1,[text2], )

Terdapat 3 argument Fungsi CONCATENATE yang memiliki arti sebagai berikut:

  1. text1 (Wajib). data pertama yang akan digabungkan, bisa berupa sebuah cell / range / data yang di ketik secara manual / hasil sebuah Rumus Excel.
  2. [text2] (Wajib), data kedua yang akan digabungkan serta juga bisa berupa sebuah cell / range / data yang di ketik secara manual / hasil sebuah Rumus Excel.
  3. (Optional). Isi argument ini jika Anda memiliki data lain yang akan digabungkan. Maksud argument ketiga ini sama dengan dan seterusnya. Artinya, Anda bisa menambahkan argument [text3], [text4], [text5], dan seterusnya.

Jika Anda perhatikan, struktur argument pada Fungsi CONCATENATE sama dengan fungsi SUM, AVERAGE, dan kawan-kawan. Setiap argument (cell / range / hasil rumus dll) bisa berada dalam sheet yang sama maupun berbeda.

Biar semakin paham, coba perhatikan secara seksama beberapa contoh menggunakan Rumus CONCATENATE berikut:

#1 Cara Menggunakan Rumus CONCATENATE Excel (Contoh Paling Dasar)

Untuk menggunakan Rumus CONCATENATE Excel, Saya menggunakan contoh data yang tampak seperti gambar berikut:

Contoh dan cara menggabungkan banyak data menjadi 1 cell menggunakan Rumus CONCATENATE Excel

Kolom A berisi data Nama Depan, sedangkan kolom B berisi data Nama Belakang. Kemudian Saya ingin menggabungkan kedua data tersebut dan hasilnya akan Saya tampilkan pada Kolom C.

Untuk tujuan tersebut, silahkan ikuti tahap-tahap menggunakan Rumus CONCATENATE berikut:

Penting! Sebelum kita mulai, Saya harap Anda sudah tahu perbedaan operator pemisah rumus (tanda koma “,” untuk format English dan tanda titik koma “;” untuk format Indonesia). Software Excel Saya menggunakan Format English. Untuk mengubah operator pemisah rumus, silahkan kunjungi Panduan Format English VS Indonesia Excel.

Contoh dan cara menggabungkan banyak data menjadi 1 cell menggunakan Rumus CONCATENATE Excel
  1. Pertama, ketik Rumus CONCATENATE berikut: =CONCATENATE( pada Cell C2
  2. Kedua, klik cell A2 sebagai argument text1 sehingga rumus menjadi =CONCATENATE(A2
  3. Ketiga, ketik operator pemisah rumus ( , atau ; )
  4. Keempat, isi argument [text2], dalam contoh ini adalah Cell B2, maka klik Cell B2 sehingga rumus menjadi =CONCATENATE(A2,B2
  5. Terakhir, ketik tanda tutup kurung ) dan tekan Enter. Berikut hasilnya:
Contoh dan cara menggabungkan banyak data menjadi 1 cell menggunakan Rumus CONCATENATE Excel

Note: Contoh ini hanya menunjukkan bagaimana cara menggunakan Rumus CONCATENATE serta cara kerjanya. Untuk contoh spesifik, bisa Anda pelajari dari contoh-contoh berikutnya.

Sedikit Penjelasan Makna Fungsi Tersebut Mungkin Bisa Membantu

Tulis Rumus / Fungsi CONCATENATE:
=
CONCATENATE(A2,B2)

Artinya:
Gabungkan data dari Cell A2 dan Cell B2

Note: Jika Anda menggunakan Excel Versi 2016 ke atas, silahkan ganti CONCATENATE menjadi CONCAT. Fungsi ini akan memberikan hasil yang sama dengan CONCATENATE.

Excel memberikan hasil RolanMardani (tanpa spasi) untuk Rumus =CONCATENATE(A2,B2). Mungkin hasilnya tidak sesuai yang diharapkan karena seharusnya Nama Depan dan Nama Belakang dipisah dengan spasi menjadi Rolan Mardani.

Tapi bukan itu tujuan Saya. Fokusnya adalah bagaimana cara Rumus CONCATENATE ini bekerja.

Seperti contoh diatas, Cell A2 berisi Rolan (tanpa spasi), sedangkan Cell B2 berisi Mardani (tanpa spasi). Jika digabungkan, maka akan menjadi RolanMardani.

Dengan kata lain, Rumus CONCATENATE benar-benar hanya menggabungkan data dari setiap argument tanpa menambah atau menguranginya.

Namun, untuk tujuan yang berbeda, mungkin Anda ingin menambahkan spasi atau text lain yang tidak ada dalam cell (tambah manual). Coba perhatikan beberapa contoh berikut:

#2 Menggabungkan Data yang Di Ketik Manual

Melanjutkan contoh sebelumnya, untuk menambahkan spasi, Anda bisa ketik spasi secara manual kedalam argument rumus CONCATENATE. Cara ini juga bisa Anda gunakan untuk menambahkan teks lain kedalam argument.

Cara menambahkan spasi pada rumus concatenate excel

Note: Untuk menambahkan Teks secara manual (seperti spasi), Anda harus mengapitnya dengan kutip 2 ” “.

Seperti hasil pada Cell D2 di atas, Saya menggunakan Rumus =CONCATENATE(A2,” “,B2). Perhatikan Saya menambahkan ” “ kedalam argument [text2]. Cell A2 berisi Rolan, Cell B2 berisi Mardani.

Jadi jika digabungkan A2 spasi B2 menjadi Rolan Mardani.

Bagaimana, apa Anda menemui kendala ? Saya harap tidak ya. Karena beberapa contoh berikut nya akan lebih kompleks lagi.

#3 Gabungan Rumus CONCATENATE dengan Rumus Lainnya

Untuk menggabungkan Rumus CONCATENATE dengan Rumus Excel lainnya, Anda bisa memasukkan rumus tambahan kedalam 1 argument.

Apa Anda masih ingat aturan penulisan rumus CONCATENATE (Lupa? Baca Lagi). Ada 3 argument. Setiap argument bisa berupa teks yang diketik manual / cell / rumus.

Pada contoh 1, Anda sudah belajar cara menambahkan teks yang diketik manual (seperti spasi). Karena teks diketik secara manual, maka Anda perlu mengapitnya dengan kutip 2 ” “.

Beda ceritanya jika Anda memasukkan Rumus lain kedalam argument Fungsi CONCATENATE, Anda tidak perlu mengapitnya dengan kutip 2.

Note: Untuk menggunakan Rumus Excel lainnya ke dalam argument CONCATENATE, setidaknya Anda juga wajib memahami aturan penulisan rumus tersebut untuk menghindari kesalahan.

Cara menggabungkan rumus CONCATENATE Excel dengan Rumus SUM

Pada Cell C8, Saya menggunakan rumus =SUM(C2:C7) dan Excel memberikan hasil 124 (hasil dari 20+32+11+16+26+19).

Sementara itu, untuk menggabungkan Fungsi CONCATENATE dengan SUM, saya menggunakan Rumus =CONCATENATE(“Total: “,SUM(C2:C7)) dan Excel memberikan Hasil Total: 124.

Penting! Fungsi SUM berada pada argument [text2] dan harus mengikuti aturan penulisan Rumus SUM pula. Jika Anda belum tahu aturan penulisannya, Silahkan kunjungi Panduan Fungsi SUM Excel.

Yang menjadi pertanyaan… Excel memberikan hasil Total: 124. Dari mana asal spasi antara Total: dengan 124 ?

Coba perhatikan argument pertama text1, Saya menyisipkan spasi setelah titik dua yaitu =CONCATENATE(“Total: . Jika tidak menyisipkan spasi disana, Excel akan memberikan hasil Total:124 (tanpa spasi).

Apa contoh di atas masih kurang ? Saya punya contoh lain nih.

#4 Rumus CONCATENATE + COUNT + COUNTIF

Kali ini Saya menggunakan 5 argument pada Rumus CONCATENATE yang mana Fungsi COUNT berada pada argument ke 2 dan COUNTIF pada argument ke 4.

Cara menggabungkan Rumus CONCATENATE Excel dengan Fungsi COUNT dan COUNTIF

Cell C10 menggunakan rumus =COUNT(C2:C7) untuk menghitung berapa banyak cell yang berisi data dari Range C2:C7. Sedangkan Cell C11 menggunakan Rumus =COUNTIF(C2:C7,”>20″) untuk menghitung berapa banyak Cell yang berisi angka lebih besar dari 20.

Sementara itu, Saya menggunakan gabungan Rumus =CONCATENATE(“Dari “,COUNT(C2:C7),” Cell yang berisi data, terdapat “,COUNTIF(C2:C7,”>20″),” Cell yang berisi angka lebih besar dari 20″) pada Cell C12.

Kemudian Excel memberikan hasil berikut: Dari 6 Cell yang berisi data, terdapat 2 Cell yang berisi angka lebih besar dari 20. Angka 6 adalah hasil dari Fungsi COUNT dan angka 2 adalah hasil dari Fungsi COUNTIF

Penting! Setidaknya Anda juga harus memahami aturan penulisan Fungsi COUNT dan COUNTIF

Dengan ini, Saya harap Anda memahami bahwa setiap argument pada Fungsi CONCATENATE bisa berisi sebuah rumus Excel.

#5 Gunakan Ampersand (Simbol &) Sebagai Alternatif Rumus CONCATENATE

Anda juga bisa menggunakan Ampersand (simbol &) sebagai pengganti fungsi CONCATENATE. Ampersand (&) ini bisa sebagai operator pemisah reference (argument jika dalam rumus). Misalnya seperti contoh berikut:

Cara menggunakan Ampersand (Simbol &) untuk menggabungkan data (teks) pada Excel menjadi 1 cell

Cell C13 menggunakan Rumus =”Total “&C8&” Unit” dan menghasilkan Total 124 Unit. Artinya, Excel akan menggabungkan teks “Total “ dan isi pada Cell C8 dan teks ” Unit”.

Sementara itu, Rumus pada Cell C14 =”Total “&SUM(C2:C7)&” “&B1 dan memberikan hasil yang sama yaitu Total 124 Unit.

Perbedaannya hanya pada argument yang digunakan. Cell C13 menggunakan Cell C8 sebagai reference yang mana cell tersebut merupakan hasil fungsi SUM.

Sedangkan Cell C14 menggunakan Fungsi SUM ke dalam argument yang mana Fungsi ini sama dengan fungsi pada Cell C8.

Plus ada tambahan sedikit tentang makna Ampersand (&) tersebut:

Tulis Rumus Ampersand (&):
=
“Total “&SUM(C2:C7)&” “&B1

Artinya:
Gabungkan
Total dan hasil dari SUM(C2:C7) dan spasi dan isi pada Cell B1

Note: Excel tidak akan menganggap spasi yang diketik tepat sebelum dan setelah ampersand (&)

Misalnya seperti rumus ini =”Total” & SUM(C2:C7) & B1. Jika itu yang Anda lakukan, Excel tetap tidak menganggap spasi tersebut dan memberikan hasil Total124Unit

Namun Saya pikir dengan menyisipkan spasi sebelum dan setelah ampersand (&), akan membuat rumus jauh lebih mudah dibaca. Ini keuntungan sebagai pengguna saja.

#6 Menggabungkan Cell yang Menggunakan Format Number Currency dan Accounting

Faktanya, Anda bisa mendapatkan hasil yang tidak sesuai dengan harapan ketika cell yang akan digabungkan menggunakan beberapa format number tertentu.

Cara menggabungkan banyak teks ke dalam 1 cell menggunakan Fungsi CONCATENATE

Seperti gambar di atas. Untuk format number menggunakan General, Number dan Text (Cell E2, E3 dan E4) tidak terdapat kesalahan.

Namun sesuatu yang ganjil terjadi pada Cell yang menggunakan Format Number Currency dan Accounting yang mana Excel tidak menyertakan Simbol Mata uang beserta format dari Cell C5 dan C6. Untuk mengatasi masalah seperti ini, salah satu solusinya gabungkan Fungsi CONCATENATE dengan TEXT.

Fungsi CONCATENATE untuk format Number Currency dan Accounting (Simbol mata uang)

Seperti yang Anda lihat, Hasil pada Cell F5 dan F6 jauh lebih tepat (menyertakan simbol mata uang serta menggunakan format yang sama. Rumus yang Saya gunakan:

Cell F5 =CONCATENATE(“Harga Normal: “,TEXT(C5,”Rp 0,000″))

Cell F6 =CONCATENATE(“Potongan Harga Sebesar “,TEXT(C6,”Rp 0,000″))

Fungsi TEXT berperan penting untuk mengubah Nilai pada Cell C5 dan C6 yang merupakan sebuah angka valid menjadi teks dengan format Rp 0,000. Setiap angka pada Cell Sumber (C5 dan C6) akan mengisi masing-masing posisi angka 0 pada Rumus TEXT.

Saya harap Anda sudah memahami aturan penulisan dan cara menggunakan Fungsi TEXT. Jika belum, silahkan kunjungi melalui Pintasan Panduan

Note: Saya menggunakan tanda koma diantara Rp 0,000 karena Software Excel Saya menggunakan Format English. Jadi sesuaikan dengan Format yang digunakan Software Anda.

Sebelumnya Saya sudah sedikit menyinggung tentang Format English VS Indonesia. Dan sudah ada link menuju panduan tersebut. Apakah Anda mengabaikan Panduan tersebut ?

#7 Menggabungkan Cell yang Menggunakan Format Number Percentage, Fraction dan Scientific

Pada dasarnya, Excel mengenal nilai pada Cell yang menggunakan Format Number Percentage, Fraction atau Scientific sebagai Bilangan Desimal.

Cara menggabungkan cell yang menggunakan format persentase, pecahan, dan scientific

Jika Anda menggunakan Fungsi CONCATENATE ataupun ampersand (&), maka Excel akan mengambil nilai desimal dari cell tersebut dan tidak menampilkan sesuai Format Number yang digunakan.

Misalnya seperti nilai pada Cell C7 berisi 25.5% (desimalnya: 0.255). Jadi jika menggunakan Fungsi CONCATENATE pada Cell tersebut, maka Excel akan menampilkan nilai desimalnya.

Untuk mengatasi keadaan ini, Anda juga bisa menggunakan gabungan Fungsi CONCATENATE dengan TEXT. Coba kita pelajari satu per satu.

Percentage (Persen)

Cara menggabungkan persentase dengan teks

Untuk Format Percentage, Saya menggunakan Rumus =CONCATENATE(“Diskon Sebesar “,TEXT(C7,”0%”)) pada cell F7. Lihat pada Fungsi TEXT, Saya menggunakan Format 0%, sehingga hasilnya Diskon Sebesar 26% (karena pembulatan dari 25.5% menjadi 26%).

Jika Anda ingin menyertakan jumlah desimal dari persentase tersebut, silahkan ubah argument “0%” menjadi “0.0%”. Ini akan menampilkan 1 angka dibelakang koma menjadi 25.5%.

Penting! Untuk menambah pemahaman tentang Persentase di Excel, silahkan kunjungi Panduan Menghitung Persentase Excel

Fraction (Pecahan)

Cara menggabungkan pecahan dengan teks di Excel

Saya menggunakan Rumus =CONCATENATE(“Total “,TEXT(C8,“0 0/0”)) pada Cell F8. Dengan ini, Excel tidak akan memberikan hasil berupa angka desimal, melainkan pecahan.

Penting! Format “0 0/0” adalah format khas dalam penulisan bilangan pecahan pada Excel. Selengkapnya bisa Anda pelajari pada Panduan Fraction Excel

Scientific (Eksponensial)

Cara menggabungkan bilangan eksponensial dengan teks di Excel

Saya tidak membuat Panduan Khusus untuk Format Number Scientific, tapi jenis format ini sedikit banyak Saya bahas pada Panduan Format Number

Dalam contoh ini, Saya menggunakan Rumus =CONCATENATE(“Probabilitas Sebesar: “,TEXT(C9,”0.00E-00″)) pada Cell F9 …daaan… Excel memberikan hasil yang sama.

Sekali lagi, Format “0.00E-00” lah yang membantu Anda.

#8 Menggabungkan Cell yang menggunakan Format Number Date dan Time

Excel menganggap Format Date (tanggal) sebagai angka bulat positif dan Format Time (waktu) sebagai angka 0 sampai 1. Saya sudah membahas kedua Format ini secara mendalam pada Panduan Date & Time Formats

Cara menggabungkan format tanggal dan waktu dengan teks dalam 1 cell

Jadi ketika Anda menggunakan Fungsi CONCATENATE, maka Excel akan menampilkan angka dari tanggal ataupun waktu tersebut.

Misalnya hasil pada Cell E10 dengan rumus =CONCATENATE(“Hari Penting: “,C10) hasilnya Hari Penting: 44615.

Note: 44615 adalah nilai sebenarnya dari tanggal 30/11/2020.

Untuk mengatasi keadaan seperti ini, Kembali gabungkan fungsi CONCATENATE dan TEXT seperti berikut:

Cara menggabungkan format tanggal dan waktu dengan teks dalam 1 cell

Cell F10 menggunakan rumus =CONCATENATE(“Hari Penting: “,TEXT(C10,”dd mmm yyyy”)). Format “dd mm yyyy” yang membantu mengubah format tanggal menjadi teks dan memberikan hasil: Hari Penting: 30 Nov 2020.

Note: Jika ingin menampilkan nomor bulan (bukan teks), silahkan ubah menjadi “dd/mm/yyyy”, maka hasilnya menjadi 30/11/2020.

Sementara itu untuk Format Number Time, Saya berikan contoh untuk Ampersand =”Event dimulai pukul ” & TEXT(C11,”hh.mm”) & ” sampai ” & TEXT(C12,”hh.mm”) pada Cell F11.

Perhatikan format “hh.mm”. h = hour dan m = minute.

Oh ya, Saya sengaja menggunakan ampersand (bukan CONCATENATE) untuk contoh ini karena pada dasarnya sama saja.

Karena format pada fungsi TEXT lah yang mengubah Format Number (baik Currency, Accounting, Percentage, Fraction, Scientific, Date, dan Time) menjadi Text.

Saya harap Anda sudah memahami semua contoh diatas sebelum lanjut ke contoh yang lebih kompleks berikut.

#9 Menggabungkan Data Banyak Cell Sekaligus (Sebuah Range)

Penting! Sebelum melanjutkan, ada baiknya pahami terlebih dahulu apa itu Range. Jika belum tahu, silahkan kunjungi Panduan Range Excel.

Untuk contoh ke 9 ini, Saya menggunakan data berikut:

Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel

Jika berdasarkan contoh-contoh sebelumnya, Anda bisa memasukkan setiap cell dan ” ” (spasi) ke dalam setiap argument. Kurang lebih rumusnya akan seperti ini:

Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel

1 Kolom =CONCATENATE(A2,”, “,A3,”, “,A4,”, “,A5,”, “,A6,”, “,A7,”, “,A8,”, “,A9,”, “,A10,”, “,A11,”, “,A12,”, dan “,A13)

1 Baris =CONCATENATE(C1,”, “,D1,”, “,E1,”, “,F1,”, “,G1,”, “,H1,”, “,I1,”, “,J1,”, “,K1,”, “,L1,”, “,M1,”, “,”, dan “,N1)

Cara tersebut tidak salah tapi tidak akan efisien jika Anda akan menggabungkan cell yang sangat banyak. Misalnya menggabungkan data pada Range A1:A100. Bayangkan seberapa panjang Rumus Anda ? Dan berapa lama waktu membuatnya ?

Muncullah Sebuah Ide.

Gunakan sebuah Range sebagai argument… Dan apa yang terjadi ???

Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel

Faktanya, jika Anda menggunakan sebuah Range sebagai argument Fungsi CONCATENATE, maka Excel hanya akan mengambil nilai dalam baris / kolom yang sama dengan Cell yang menggunakan Rumus.

Seperti hasil pada Cell E7 menggunakan rumus =CONCATENATE(C1: N1). Excel hanya mengambil Nilai pada Kolom yang sama (Kolom E) dari range data yaitu Mar.

Note: Jika Anda menggunakan Rumus CONCATENATE pada kolom yang tidak ada dari range data (Kolom C sampai N) pada argument, maka Excel memberikan hasil #VALUE!

Hasil Error ini bisa Anda lihat pada Cell Q1 dan Q7.

Sebaliknya jika Range data berupa 1 kolom dan banyak baris. Rumus Cell E8 =CONCATENATE(A2:A13) juga hanya mengambil nilai pada baris yang sama dengan range data yaitu Jul.

Begitu juga jika Anda menggunakan Rumus tersebut bukan pada cell yang 1 baris dengan range data (Baris 2 Sampai 13), maka Excel memberikan hasil #VALUE! seperti cell A15 dan E15.

Lalu bagaimana solusinya ???

Penting! Trik ini membutuhkan pemahaman dalam menggunakan ARRAY Formula, terutama Dimensi dan Konstanta. Jika belum tahu, silahkan pelajari Panduan Rumus ARRAY Excel terlebih dahulu.

Jika Range Data Terdiri Dari 1 Kolom dan Banyak Baris

Ada 5 tahap untuk menggabungkan Data Dari Range 1 Kolom dan Banyak Baris seperti berikut:

  1. Masukkan Rumus =CONCATENATE(A2:A13&”, “) setelah itu jangan buru-buru tekan enter. Lihat tahap 2. Note: argument &”, ” pada rumus berguna untuk menyisipkan 1x koma dan spasi secara otomatis pada setiap data yang akan digabungkan.
    Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel
  2. Pilih (Blok) Argument pada rumus. Dalam contoh ini A2:A13&”, “.
    Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel
  3. Tekan Tombol F9 pada Keyboard untuk mengekstrak Konstanta ARRAY. Note: sebagian komputer perlu menekan tombol FN dan F9 secara bersamaan. Jadi setelah menekan tombol F9, pastikan argument rumus CONCATENATE berubah menjadi seperti berikut:
    Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel
  4. Hapus Kurung Kurawal yang mengapit argument rumus {…} sehingga hanya menyisakan Buka Kurung dan tutup kurung yang mengapit argument.
    Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel menggunakan Rumus CONCATENATE
  5. Tekan Enter dan berikut hasilnya:
    Cara menggabungkan teks dalam sebuah range menjadi 1 cell di Excel menggunakan Rumus CONCATENATE

Argument &”, ” pada rumus =CONCATENATE(A2:A13&”, “) berguna untuk menyisipkan koma dan spasi setelah setiap list menjadi Jan, Feb, , Nov, Des. Namun, tentu jauh lebih bagus jika menyisipkan teks “dan” sebelum list terakhir …,Okt, Nov, dan Des.

Jika Anda sudah berhasil melakukan 5 tahap di atas dan tidak mendapatkan notif error, silahkan pelajari cara menambahkan “dan” di akhir list di bawah (klik disini)

Kenapa Saya Tidak Bisa dan Ada Notif Muncul Ya ?

Yess… Anda bukan satu-satunya orang yang mengalami hal tersebut. Dulu, Saya juga pernah. Lihat orang lain bisa dan saat kita coba tidak bisa memang menyebalkan.

Solusi Rumus CONCATENATE Error (gagal)

Jika mendapatkan notif seperti ini, klik Ok aja. Terus… Perhatikan Operator Pemisah Konstanta setelah menekan tombol F9.

Excel memisahkan setiap list konstanta (untuk 1 dimensi vertikal) menggunakan tanda titik koma (;).

Note: Sebelumnya sudah Saya ingatkan, pahami operator pemisah rumus ( , atau ; ) dan sudah Saya share link Panduannya. Masih diabaikan kah panduannya ? 😀

Maaf ya, Saya agak nyinyir tentang ini. Soalnya operator pemisah rumus ini adalah materi paling dasar sebelum menggunakan Rumus / Fungsi Excel yang Wajib dipahami dan sering pula diabaikan.

Saya yakin Anda sudah paham tentang operator pemisah rumus ini… Jadi kita balik ke solusi permasalahan ini…

Solusi Rumus CONCATENATE Error (gagal)
  1. Pertama, Edit rumus dan tambahkan karakter apapun sebelum =. Dalam contoh ini, Saya menggunakan huruf A. Kemudian tekan Enter.
  2. Kedua, Buka Fitur Replace dengan cara tekan tombol CTRL dan H pada keyboard secara bersamaan.
  3. Ketiga, pada Window Find & Select, masukkan karakter yang akan dicari pada kolom Find what dan karakter pengganti pada kolom Replace with. Dalam contoh ini, Saya akan mengganti titik koma ( ; ) menjadi koma ( , ). Sesuaikan dengan tujuan Anda.
  4. Keempat, klik Replace All kemudian klik Ok pada notif.
  5. Kelima, hapus kembali Huruf / karakter sebelum tanda sama dengan =. Dalam contoh ini huruf A. dan tekan Enter. Hasilnya sudah pasti sama dengan contoh di atas.

Penting! Selengkapnya tentang fitur Find & Replace bisa Anda pelajari pada Panduan Find and Select Excel.

Oh ya, hati-hati menggunakan Fitur Replace ini. Karena akan mengganti semua karakter yang dicari dalam Sheet Anda. Jadi ada kemungkinan data yang tidak perlu diubah akan berubah.

Jika Anda ragu, silahkan Copy-Paste terlebih dahulu Rumus dalam Cell A16 ke sheet kosong. Kemudian ulangi tahap 2 sampai 5 diatas. Terakhir, Copy-Paste kembali Rumus tersebut ke cell A16. Beres deh…

Tambahkan Teks “dan” Sebelum List Terakhir

Hasil dari contoh di atas tidak menyertakan teks “dan” sebelum list terakhir yaitu Jan, Feb, …, Nov, Des. Tentu jauh lebih cocok jika menggunakan teks “dan” menjadi Jan, Feb, …, Nov, dan Des.

Untuk tujuan tersebut, Anda bisa menggunakan 3 argument Rumus CONCATENATE seperti berikut:

Tulis Rumus / Fungsi CONCATENATE Range:
=
CONCATENATE(A2:A12&”, “,“dan “,A13)

Lihat, range data pada argument pertama hanya sampai list ke dua dari terakhir A2:A12, Argument kedua berupa teks “dan “. Dan terakhir, argument ke tiga berupa list data terakhir A13.

Cara menambahkan teks "dan" pada akhir list rumus CONCATENATE Excel

Oh ya, setelah ketik rumus tersebut, silahkan ulangi tahap 2 sampai 5 sebelumnya. Yang perlu diperhatikan adalah sebelum tekan tombol F9, Anda hanya perlu pilih (blok) argument pertama A2:A12&”, “.

Selanjutnya tidak ada perbedaan pada tahap-tahap lainnya.

Jika Range Data Terdiri Dari 1 Baris dan Banyak Kolom

Untuk menggabungkan data setiap cell pada Range yang terdiri dari 1 Baris dan Banyak Kolom, Anda membutuhkan bantuan Fungsi TRANSPOSE.

Tulis Rumus / Fungsi CONCATENATE Range + TRANSPOSE:
=
CONCATENATE(TRANSPOSE(C1:M1&”, “),“dan “,N1)

Setelah mengetik Rumus tersebut, silahkan pilih (blok) argument pertama rumus CONCATENATE yaitu TRANSPOSE(C1:M1&”, “) seperti gambar berikut:

Cara menggabungkan range data menggunakan Fungsi CONCATENATE Excel

Kemudian tekan tombol F9, maka argument pertama akan berubah menjadi:

Cara menggabungkan range data menggunakan Fungsi CONCATENATE Excel

Perbedaannya hanya Fungsi TRANSPOSE saja. Selanjutnya, lakukan tahap 4 dan 5 seperti cara sebelumnya. Apabila tidak bisa dan ada notif, Anda juga bisa ulangi solusi pada tahap 1 – 5 yang sudah Saya bahas sebelumnya. Jika berhasil, maka hasilnya akan tampak seperti gambar berikut:

Cara menggabungkan range data menggunakan Fungsi CONCATENATE Excel

Jika Range Data Terdiri Dari Banyak Baris dan Kolom

Misalnya, Saya memiliki data pada range A1:E3 seperti gambar berikut:

Cara menggabungkan range data menggunakan Fungsi CONCATENATE Excel

Kemudian, Saya ingin menggabungkan semua isi dari setiap cell pada Range data tersebut serta menyisipkan tanda koma dan spasi antara setiap list menjadi 1 cell.

Jadi Saya hanya menggunakan Rumus ini: =CONCATENATE(A1:E3&”, “) kemudian pilih (blok) argument A1:E3&”, “ dan tekan F9.

Selanjutnya, lakukan hal-hal yang sama dengan kedua contoh sebelumnya seperti menghapus kurung kurawal, atau memperbaiki kesalahan (jika terjadi). Jika berhasil, berikut hasil yang Saya dapatkan:

Cara menggabungkan range data menggunakan Fungsi CONCATENATE Excel

Note: Setelah tekan F9, periksa dengan cermat operator pemisah Konstanta ARRAY.

Jika Anda menggunakan Format Indonesia, Excel akan mengubahnya menjadi backslash “\” dan titik koma (;).

Jika menggunakan format English, Excel akan menggunakan tanda titik koma dan koma.

#10 Ampersand (&) + CHAR(10) untuk Wrap Text

Menggabungkan Ampersand dengan fungsi CHAR(10) bisa memaksimalkan fitur Line Break – Wrap Text pada Excel. Gunakan cara ini jika Anda ingin memisahkan data yang digabungkan menjadi beberapa baris dalam 1 cell.

Menggabungkan Ampersand (&) dan Fungsi CHAR(10) untuk menggabungkan teks dan cell menjadi 1 cell

Saya menggunakan gabungan ampersand dan Fungsi CHAR(10) seperti berikut: =A2 & CHAR(10) & B2 & CHAR(10) & C2 & CHAR(10) & D2 pada Cell E4.

Note: Jangan lupa mengaktifkan fitur Wrap text melalui Tab Home > Wrap Text.

Seperti yang Anda lihat, setiap data yang digabungkan tampil dalam beberapa baris di dalam 1 cell yang sama. Meskipun Kolom F memiliki lebar yang cukup untuk menampilkan hasil rumus. Teknik ini sama dengan Line Break Wrap Text yang biasanya dibuat secara manual.

#11 Rumus CONCATENATE Beda Sheet

Anda juga bisa menggunakan Fungsi CONCATENATE untuk menggabungkan data antar sheet.

Seperti penjelasan pada aturan Penulisan Rumus CONCATENATE di atas, setiap argument dapat berisi sebuah data (ketik manual), cell, range, baik dalam sheet yang sama maupun sheet berbeda. Misalnya seperti contoh berikut:

Penting! Untuk memudahkan pekerjaan, gunakan Fitur View Multiple WorkSheet untuk melihat banyak sheet secara bersamaan.

Cara menggunakan Rumus CONCATENATE Beda Sheet

Perhatikan argument rumus pada Cell C2 yaitu =CONCATENATE(A2,” “,BedaSheet2!A2). Argument pertama A2 yaitu Cell yang berada pada Sheet yang sama dengan cell yang menggunakan Rumus CONCATENATE.

Reference ini sudah umum dan sudah kita pelajari semuanya dalam panduan ini.

Tapi lihat argument ke 3 yaitu BedaSheet2!A2, Anda harus menyertakan nama Sheet dan diakhiri tanda seru (!). Kemudian Nama cell (A2).

Namun jika nama Sheet kedua mengandung spasi, maka Anda harus mengapitnya dengan kurung satu ‘…’. Misalnya Beda Sheet2!A2. Hal ini sudah umum dalam penggunaan rumus antar Sheet.

Penting! Saya harap Anda memahami hal-hal paling dasar dalam menggunakan Rumus Beda Sheet. Semuanya sudah Saya bahas pada Panduan 3D Reference Excel.

BONUS: Alternatif Rumus CONCATENATE

Jika beberapa contoh menggunakan Rumus CONCATENATE diatas belum menjawab permasalahan Anda, coba perhatikan beberapa alternatif berikut:

#1 Rumus TEXTJOIN (Alternatif CONCATENATE)

Fungsi TEXTJOIN tersedia pada Excel versi 2019 ke atas. Secara umum, penggunaan Fungsi TEXTJOIN sama saja dengan fungsi CONCAT (Excel 2016 ke atas), dan CONCATENATE.

Perbedaan utamanya, Fungsi TEXTJOIN dapat menentukan pemisah / pembatas antar data yang akan digabungkan dengan mempertimbangkan cell kosong atau tidak.

Alternatif Rumus CONCATENATE Excel

Seperti contoh di atas. Jika menggunakan Rumus CONCATENATE, maka Anda bisa menggunakan =CONCATENATE(A2,” “,B2,” “,C2,” “,D2). Sejauh ini tidak ada kesalahan yang terjadi.

Namun, coba gabungkan data pada baris selanjutnya. Gunakan fitur AutoFill atau Copy-Paste Rumus pada Cell E2 ke cell E3 sampai E9. Lihat beberapa kesalahan terjadi:

Fungsi TEXTJOIN sebagai Alternatif Rumus CONCATENATE Excel

Misalnya Row 3 (Budi Sulistiono). Nama ini tidak memiliki title serta nama tengah. Sementara itu, rumus yang digunakan =CONCATENATE(A3,” “,B3,” “,C3,” “,D3).

Karena Cell A3 dan C3 kosong, maka Excel hanya akan menggabungkan data berikut: ?_spasi_Budi_spasi_?_spasi_Sulistiono.

Note: Tanda tanya (?) adalah posisi Cell A3 dan C3. Karena kosong, maka tidak ada data yang digabungkan. Akibatnya ada spasi berlebih sebelum dan setelah teks “Budi”.

Fungsi TEXTJOIN sebagai Alternatif Rumus CONCATENATE Excel

Gunakan Fungsi TEXTJOIN untuk mengabaikan cell kosong dalam menggabungkan data. Misalnya seperti Cell F3 =TEXTJOIN(” “,TRUE,A3: D3) dan hasilnya jauh lebih tepat daripada menggunakan fungsi CONCATENATE.

Oh ya, Ada contoh lebih kompleks lagi nih… Saya menggabungkan Fungsi TEXTJOIN, Fungsi IF dan Named Range untuk menggabungkan data berdasarkan Syarat.

Kombinasi Rumus TEXTJOIN dengan Fungsi IF dan Named Range untuk menggabungkan list data ke dalam 1 cell

Dengan menggunakan Rumus {=TEXTJOIN(“, “,TRUE,IF(Grup=E3,Anggota,””))}, Saya bisa menggabungkan Nama Anggota berdasarkan Grup.

Oh ya, Kurung Kurawal {…} yang mengapit rumus termasuk ARRAY Formula. Anda tidak boleh mengetiknya secara manual karena Excel secara otomatis menyisipkannya.

Penting! Selengkapnya lihat contoh mendalam tentang Rumus TEXTJOIN pada Panduan Fungsi TEXTJOIN Excel

#2 Fitur Fill Justify

Cara menggabungkan list data menjadi 1 cell menggunakan fitur Fill Justify Excel

Fitur Fill Justify bisa Anda gunakan untuk menggabungkan data setiap cell menjadi 1 dengan mengubah data asli. Setelah menggunakan Fill Justify, setiap baris data pada contoh di atas akan tergabung seperti berikut:

Cara menggabungkan list data menjadi 1 cell menggunakan fitur Fill Justify Excel

Penting! Jika ini yang Anda butuhkan, silahkan pelajari selengkapnya pada Panduan Fill Justify Excel.

Bagaimana, apakah Panduan ini dapat membantu menyelesaikan permasalahan Anda ? Mudah-mudahan sudah rampung ya…

Tapi, meskipun Saya memberikan beberapa contoh yang berbeda, Saya yakin ada banyak sekali keadaan lain yang mungkin Anda alami dan belum Saya bahas pada panduan ini.

Jika Anda memiliki permasalahan yang berbeda atau solusi lain yang bisa menyelesaikan permasalahan dalam menggabungkan data pada Excel, silahkan coret-coret kolom komentar ya… Atau gunakan Pintasan Panduan berikut:

Pintasan Panduan Text Function Excel

  1. Text Functions: Cara menggabungkan kata pada 2 atau lebih cell, mengambil kata tertentu pada cell, replace kata tertentu tanpa mengubah kata aslinya.
  2. Separate Strings: Cara memisahkan kata pada cell tanpa mengubah nilai aslinya.
  3. Count Words: Cara menghitung jumlah karakter, menghapus spasi yang berlebihan, dll (lengkap)
  4. Text to Columns: Cara memisahkan konten dalam 1 cell menjadi kolom terpisah. Biasanya terjadi pada file csv yang di download dan sering dianggap berantakan.
  5. Find Function: Cara menggunakan Fungsi Find untuk menemukan karakter tertentu didalam file Excel Anda.
  6. SEARCH: Cara menggunakan Fungsi Search di Excel untuk mencari posisi karakter pada cell tertentu.
  7. Change Case: Cara otomatis mengubah kata menjadi huruf kapital.
  8. Remove Spaces: Cara menghapus spasi atau spasi berlebihan pada Cell. Tersedia juga cara menghapus karakter khusus pada cell.
  9. Compare Text: Cara membandingkan 2 cell dengan isi yang sama. Contoh ini menggunakan metode pembanding peka huruf besar & kecil dan tidak.
  10. Subtitute VS Replace: Cara menggunakan Fungsi Subtitute dan Replace (lengkap) untuk mengganti kata / karakter tertentu pada cell.
  11. Text: Cara menggunakan Fungsi Text untuk menggabungkan angka dan huruf kedalam 1 cell.
  12. CONCATENATE: (Anda Disini)
  13. Substring: Menggabungkan fungsi MID, LEFT, RIGHT, FIND, LEN, SUBTITUTE, REPT, TRIM dan MAX untuk mengekstrak karakter tertentu pada cell. Contoh, mengambil angka pada kalimat yang berbeda.

Komentar Paling Lambat di Balas Pukul 23:59.