Data yang anda miliki pada Microsoft Excel tidak selalu terstruktur sesuai dengan kebutuhan. Terkadang kita perlu memisahkan isi dalam suatu sel menjadi beberapa sel, atau melakukan sebaliknya - menggabungkan data dari beberapa sel atau lebih menjadi satu sel tunggal.
Contoh umum yang membutuhkan penggabungan dalam Excel adalah menggabungkan nama depan dan nama belakang seseorang, menggabungkan alamat lengkap, menggabungkan teks dengan nilai hasil perhitungan formula, menampilkan tanggal dan waktu dalam format yang diinginkan, dan masih banyak lagi.
Artikel ini membahas tentang berbagai teknik penggabungan isi sel pada Excel, dan anda dapat memilih metode yang paling pas dengan kebutuhan anda:
- Fungsi Concatenate / Concat
- Operator Ampersand (&)
- Menggabungkan dengan jeda spasi, koma atau karakter lainnya
- Menggabungkan dengan jeda baris / enter
- Menggabungkan angka dan tanggal dalam format yang diinginkan
1. Fungsi Concatenate / Concat
Fungsi CONCATENATE di Excel dirancang untuk menggabungkan potongan teks yang berbeda bersama-sama atau menggabungkan nilai dari beberapa sel menjadi satu sel. Fungsi CONCATENATE memiliki batasan hingga 255 gabungan, semuanya dipisahkan oleh koma. Argumen dapat berupa string teks, angka, atau referensi sel tunggal.Sintaks CONCATENATE adalah sebagai berikut:
CONCATENATE (text1, [text2], …)
Di mana "text" dapat diisi dengan string teks, referensi sel lain, atau hasil perhitungan rumus. Selain itu, untuk lebih mudah dalam pengisian argumen CONCATENATE, Anda juga dapat mengisinya melalui kotak dialog Function Arguments.
Catatan. Di Excel 2016, Excel 2019, Excel Online dan Excel Mobile, CONCATENATE diganti dengan fungsi CONCAT, yang memiliki sintaks yang sama persis. Meskipun fungsi CONCATENATE masih dapat digunakan untuk kompatibilitas terhadap versi lama (sebelum excel 2016), namun disarankan untuk menggunakan CONCAT sebagai gantinya karena Microsoft tidak berjanji bahwa CONCATENATE masih akan disediakan di versi Excel yang akan datang.
^ Kembali ke Daftar Isi
2. Operator Ampersand (&)
Di Microsoft Excel, operator Ampersand (&) adalah cara lain untuk menggabungkan sel. Metode ini sangat berguna dalam banyak skenario karena mengetikkan tanda "&" jauh lebih cepat daripada mengetik kata "concatenate".Mirip dengan fungsi CONCATENATE, Anda bisa menggunakan "&" di Excel untuk menggabungkan berbagai string teks, nilai sel, dan hasil yang dikembalikan oleh fungsi lain.
Penggunaan Ampersand (&) adalah sebagai berikut:
= text1 & text2 & text3 & …
Di mana "text" dapat diisi dengan string teks, referensi sel lain, atau hasil perhitungan rumus.
^ Kembali ke Daftar Isi
3. Menggabungkan dengan jeda spasi, koma atau karakter lainnya
Di lembar kerja Anda, Anda mungkin sering harus menggabungkan beberapa nilai dengan menambahkan koma, spasi, berbagai tanda baca atau karakter lain seperti tanda hubung atau garis miring. Untuk melakukannya, cukup sertakan karakter yang Anda inginkan dalam formula concatenation atau ampersand. Ingatlah untuk melampirkan karakter tersebut di dalam tanda kutip, seperti yang ditunjukkan dalam contoh berikut.Menggabungkan dua sel dengan spasi:
= CONCATENATE (A1, " ", B1) atau = A1 & " " & B1
Menggabungkan dua sel dengan koma:
= CONCATENATE (A1, ", ", B1) atau = A1 & ", " & B1
Menggabungkan dua sel dengan tanda hubung:
= CONCATENATE (A1, "-", B1) atau = A1 & "-" & B1
Screen shot berikut menunjukkan bagaimana hasil dari beberapa rumus tersebut:
^ Kembali ke Daftar Isi
4. Menggabungkan dengan jeda baris / enter
Hal yang paling umum terjadi yaitu memisahkan string teks ataupun kalimat dengan tanda baca dan spasi, seperti yang ditunjukkan pada contoh sebelumnya. Namun, dalam beberapa kasus, mungkin perlu memisahkan teks dengan Enter (pc), atau carriage return (mac). Contoh umum adalah menggabungkan nama dengan deskripsi, alamat, dll.Masalahnya adalah Anda tidak bisa hanya mengetikkan enter dalam rumus concatenate / ampersand seperti karakter biasa, dan karena itu diperlukan fungsi CHAR khusus untuk memasok kode ASCII yang sesuai ke rumus concatenate/ampersand:
- Di sistem Windows, gunakan CHAR(10) di mana kode 10 adalah kode ASCII untuk Ganti Baris / Enter (Line feed).
- Pada sistem Mac, gunakan CHAR(13) di mana kode 13 adalah kode ASCII untuk return (carriage return).
=A2 & " " & B2 & CHAR(10) & C2 & CHAR(10) & D2 & ", " & E2 & " " & F2
Catatan. Saat menggunakan jeda baris / Enter, Anda harus mengaktifkan opsi "Wrap Text" agar hasilnya ditampilkan dengan benar. Untuk melakukan ini, tekan Ctrl + 1 (control + satu) untuk membuka dialog Format Cells, beralih ke tab Alignment dan centang kotak Wrap Text, atau lebih mudahnya klik tombol Wrap Text di menu Home bagian Alignment seperti gambar di bawah ini.
Dengan cara yang sama, Anda dapat memasukkan karakter lain seperti:
- Kutipan/Petik ganda (") - CHAR (34)
- Garis miring (/) - CHAR (47)
- Asterisk (*) - CHAR (42)
Namun cara lebih cepat dan mudah untuk memasukkan karakter tersebut adalah dengan mengetikkannya langsung dalam tanda kutip ganda seperti yang kita lakukan pada Sub Bab 3 sebelumnya.
^ Kembali ke Daftar Isi
5. Menggabungkan angka dan tanggal dalam format yang diinginkan
Sebagian dari kita mungkin telah mencoba menerapkan fungsi Concatenate maupun operator Ampersand di Excel, untuk menggabungkan cell dengan format tanggal (1/19/2019), angka seperti mata uang (Rp 7.345,00), persentase (48.9%), desimal (24.50000), dll. Namun format tersebut akan hilang dan ditampilkan dalam cell hasil sebagai angka umum seperti screenshot di bawah ini (kolom G). Ada beberapa solusi untuk menggabungkan data dengan menjaga format tanggal atau angka di Excel seperti pada kolom I di bawah ini.
Dalam kasus ini, kita menggunakan fungsi tambahan, yaitu fungsi TEXT. Dalam kasus ini, kita menggunakan fungsi tambahan, yaitu fungsi TEXT. Fungsi TEXT memproses nilai angka dalam format tertentu, dan menjadikannya sebagai teks. Anda dapat menggunakan fungsi TEXT untuk menyisipkan angka ke dalam sebuah teks.
Syntaks nya yaitu:
TEXT(value, format_text)
Argument value diisi dengan angka yang akan dikonversi, dan format_text diisi dengan Format angka yang akan digunakan.
Menggabungkan dengan format tanggal
Untuk menggabungkan format tanggal, kita menggunakan kode "dd"(hari), "mm" (bulan) dan "yyyy" (tahun). dan kita dapat memberikan pemisah tanggal sesuai dengan keinginan (spasi, tanda hubung, garis miring, dll). Anda dapat mengubah kode "dd/mm/yyyy" ke kode format tanggal tertentu sesuai kebutuhan seperti pada gambar dibawah ini.
pada baris kedua dan kelima, menggunakan garis miring sebagai pemisah tanggal, dengan rumus:
= CONCATENATE(TEXT(A2, "dd/mm/yyyy"), " - ", B2) atau bisa juga menggunakan = TEXT(A5, "dd/mm/yyyy") & " - " & B5
pada baris ketiga dan keenam, menggunakan tanda hubung "-" sebagai pemisah tanggal, dengan rumus:
= CONCATENATE(TEXT(A3, "dd-mm-yyyy"), " - ", B3) atau bisa juga menggunakan = TEXT(A6, "dd-mm-yyyy") & " - " & B6
pada baris keempat (dengan pemisah spasi), untuk menggunakan tanggal panjang, kita dapat menggunakan kode "mmmm" untuk nama bulan nya (contoh penggunaan dalam Concatenate):
= CONCATENATE(TEXT(A4, "dd mmmm yyyy"), " - ", B4) atau bisa juga menggunakan = TEXT(A4, "dd mmmm yyyy") & " - " & B4
dan untuk nama bulan dalam bahasa indonesia seperti baris ketujuh, kita tetap menggunakan kode "mmmm", namun dengan menambahkan kode [$-id-ID] di awal format:
= CONCATENATE(TEXT(A7, "[$-id-ID] dd mmmm yyyy"), " - ", B7) atau bisa juga menggunakan = TEXT(A7, "[$-id-ID] dd mmmm yyyy") & " - " & B7
Menggabungkan dengan format angka (mata uang / persentase / desimal)
untuk format yang umum digunakan, kita dapat menggunakan kode berikut:
Format mata uang = Rp #,##0.00 Format persentase = 0.00% Format desimal/pecahan = #,#0.000
.000 dan .00 pada presentase/desimal dapat digunakan untuk menentukan jumlah angka dibelakang koma. Misal .000 berarti 3 angka dibelakang koma. Penggunaannya dalam fungsi Concatenate maupun operator Ampersand dapat dilihat pada gambar di bawah ini, yang sekaligus menggunakan format tanggal, mata uang, dan juga persentase. Rumus yang digunakan yaitu:
= TEXT(A2, "dd/mm/yyyy") & " | " & B2 & ": "
& TEXT(C2, "Rp #,##0.00") & " (" & TEXT(D2, "0.00%")
atau bisa juga dengan
= CONCATENATE(TEXT(A2, "dd/mm/yyyy"), " | ", B2, ": ",
TEXT(C2, "Rp #,##0.00"), " (", TEXT(D2, "0.00%"), ")" )
Namun terkadang data menggunakan format lain. Untuk kasus tersebut, pertama-tama, kita perlu mendapatkan kode format angka yang akan digabungkan: klik kanan pada sel yang berisi angka dan pilih "Format Cell". Lihat screen shot di bawah ini:
Di kotak dialog Format Cell, buka tab Number, klik Custom pada kotak Category, salin/copy isi kode format yang berada di kotak Type, lalu tutup kotak dialog Format Cell:
lalu masukkan kode format tersebut ke dalam fungsi TEXT.
Catatan. terkadang kode format mata uang yang Anda salin mungkin sangat rumit yang menyebabkan error/kesalahan rumus. Jika demikian, maka gunakan format sesederhana mungkin seperti Rp #,##0.00
contoh format yang dapat membuat fungsi TEXT menjadi error :
_-[$Rp-id-ID]* #,##0.00_-;-[$Rp-id-ID]* #,##0.00_-;_-[$Rp-id-ID]* "-"??_-;_-@_-
yang dapat kita ganti menjadi Rp #,##0.00
^ Kembali ke Daftar Isi
___________________________
Referensi:
Extend Office. 2017. How to concatenate keeping number/date formatting in Excel?. extendoffice.com
Cheusheva, Svetlana. 2018. CONCATENATE in Excel: combine text strings, cells and columns. ablebits.com
Excel Semi-Pro. 2010. CONCATENATE Function or Ampersand Operator in Excel. excelsemipro.com
Bruns, Dave; Bruns, Lisa. Excel TEXT Function. exceljet.net