Formula umum
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")
Ringkasan
Untuk menggabungkan beberapa nilai sel dengan koma, Anda dapat menggunakan rumus berdasarkan fungsi SUBSTITUTE dan TRIM. Anda dapat menggunakan pendekatan yang sama ini untuk menggabungkan nilai dalam sel dengan pembatas apa pun yang Anda suka. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=SUBSTITUTE(TRIM(B5&" "&C5&" "&D5&" "&E5&" "&F5)," ",", ")
Penjelasan
Bekerja dari dalam ke luar, rumus pertama menggabungkan nilai 5 sel ke kiri menggunakan operator penggabungan (&) dan satu spasi di antara setiap nilai:
B5&" "&C5&" "&D5&" "&E5&" "&F5
Bagian dari rumus ini sangat manual. Untuk mempercepat, salin & "" & ke papan klip sebelum Anda mulai. Kemudian ikuti pola ini:
(klik) (tempel) (klik) (tempel) (klik) (tempel)
sampai Anda mendapatkan referensi sel terakhir. Ini sebenarnya berjalan cukup lama.
Hasil dari penggabungan ini (sebelum TRIM dan SUBSTITUTE dijalankan) adalah string seperti ini:
"figs apples "
Selanjutnya, fungsi TRIM digunakan untuk "menormalkan" semua jarak. TRIM secara otomatis menghapus spasi di awal dan akhir string tertentu, dan hanya menyisakan satu spasi di antara semua kata di dalam string. Ini menangani ruang ekstra yang disebabkan oleh sel kosong.
"figs apples"
Terakhir, SUBSTITUTE digunakan untuk mengganti setiap spasi ("") dengan koma dan spasi (","), mengembalikan teks seperti ini:
"figs, apples"
Menggabungkan sel dengan pembatas lain
Untuk menggabungkan sel dengan pembatas (pemisah) lain, cukup sesuaikan argumen "new_text" di dalam SUBSTITUTE. Misalnya, untuk menggabungkan sel dengan garis miring ke depan, gunakan:
=SUBSTITUTE(TRIM(B7&" "&C7&" "&D7&" "&E7&" "&F7)," ","/")
Outputnya akan terlihat seperti ini:
limes/apricots/apricots/limes/figs
Fungsi TEXTJOIN
Fungsi TEXTJOIN adalah fungsi baru yang tersedia di Office 365 dan Excel 2019. TEXTJOIN memungkinkan Anda menggabungkan rentang sel dengan pemisah. Dengan TEXTJOIN, contoh di atas akan terlihat seperti ini:
=TEXTJOIN(", ",TRUE,B5:F5)
Makro
Jon Acampora di ExcelCampus memiliki makro keren untuk menggabungkan berbagai sel.
