Salah satu masalah dalam menangani data kontak adalah format nama lengkap mereka. Untuk kontak yang nama lengkapnya muncul sebagai First Last, Anda mungkin lebih suka memformatnya sebagai Last, First
Melakukannya biasanya mengarah pada penyortiran dan pemfilteran yang lebih mudah dalam daftar Anda. Juga, nama belakang umumnya lebih unik daripada nama depan yang membuat pencarian lebih dapat diandalkan
Posting ini mengeksplorasi beberapa cara berbeda untuk membalikkan nama depan dan belakang di Excel
Ganti Nama Depan dan Belakang dengan Teks ke Kolom
Salah satu cara untuk mengganti nama depan dan belakang adalah melalui fitur yang disebut Teks ke Kolom
Fitur ini dapat memisahkan nama depan dan belakang ke dalam sel mereka sendiri, di mana mereka dapat digabungkan kembali ke dalam format baru menggunakan rumus
Untuk memulai, pilih sel yang berisi kontak Anda
Buka tab pita Data dan klik Teks ke Kolom
Pastikan Dibatasi dipilih sebagai tipe data Asli Anda. Ini karena karakter spasi bertindak sebagai pemisah antara nama depan dan belakang
Klik tombol Next jika sudah siap
Dari Pembatas di atas, centang opsi Ruang sebagai pembatas Anda dan kosongkan semua kotak centang Pembatas lainnya
Pratinjau Data memperlihatkan bagaimana Excel akan memisahkan nama
Klik tombol Berikutnya
Pilih kotak teks Tujuan, lalu pilih satu sel yang akan menjadi titik awal dari nama Anda yang terpisah
Dalam contoh di atas, nama depan kontak pertama Anda akan masuk ke sel FIND ( " " , B3 )3 sedangkan nama belakang akan masuk ke sel sebelah kanan. Kontak Anda yang lain akan terpisah dengan cara yang sama ke bawah
Klik tombol Selesai
Seperti yang Anda lihat di atas, nama depan dan belakang telah dipisahkan ke dalam kolomnya masing-masing
= D3 & ", " & C3_Langkah selanjutnya adalah menggabungkan kembali kolom untuk mengganti nama
Di baris kontak pertama Anda, pilih sel kosong dan rekatkan rumus di atas ke bilah rumus
Ampersand (FIND ( " " , B3 )_4) menggabungkan nama belakang dari sel D3 dengan nama depan dari sel FIND ( " " , B3 )5 dan memisahkannya dengan koma (FIND ( " " , B3 )6) di antaranya
Tekan Enter untuk menghitung rumus
Untuk menerapkan rumus ke kontak lain, arahkan kursor ke sudut kanan bawah sel rumus hingga kursor menjadi plus (FIND ( " " , B3 )7), lalu klik kiri dan tahan tombol tetikus sambil menyeret ke bawah
Nama kontak Anda sekarang diganti
Ganti Nama Depan dan Belakang dengan Formula Teks
Jika Anda lebih suka menggunakan solusi formula saja untuk mengganti nama, ada cara untuk melakukannya
Rumus seperti itu akan menambahkan hasil dari fungsi FIND, LEN, RIGHT dan LEFT untuk memformat setiap nama ke dalam sel baru
FIND ( " " , B3 )Diberi nama lengkap di sel B3, fungsi FIND di atas mengembalikan posisi karakter spasi, mulai dari kiri
LEN ( B3 ) - FIND ( " " , B3 )Untuk menghitung posisi karakter spasi dimulai dari kanan, hasil FIND dikurangi dari panjang nama lengkap
RIGHT ( B3 , LEN ( B3 ) - FIND ( " " , B3 ) )_Nama belakang diekstrak menggunakan fungsi RIGHT di atas
LEFT ( B4 , FIND ( " " , B4 ) - 1 )_Mengekstrak nama depan serupa, seperti yang ditunjukkan di atas. Tapi FIND ( " " , B3 )8 dikurangi dari hasil FIND sehingga karakter spasi dikecualikan
= RIGHT ( B3, LEN ( B3 ) - FIND ( " ", B3 ) ) & ", " & LEFT ( B3 , FIND ( " " , B3 ) - 1 )Gabungkan semua ini, rekatkan rumus di atas ke dalam sel kosong di sebelah kanan kontak pertama Anda. Rumus menggabungkan nama belakang dan nama depan, dengan koma (FIND ( " " , B3 )6) di antaranya.
Tekan Enter untuk memicu perhitungan rumus
Untuk menerapkan rumus ke kontak Anda yang lain, arahkan kursor ke sudut kanan bawah sel rumus hingga kursor Anda menjadi plus (+), lalu klik kiri dan tahan tombol tetikus sambil menyeret ke bawah
Voila. Pemformatan nama Anda selesai
Ganti Nama Depan dan Belakang dengan Fungsi TEXTSPLIT
TEXTSPLIT adalah fungsi baru yang diperkenalkan di Excel 365 beta versi 2203
Fungsi TEXTSPLIT memisahkan nilai sel menjadi beberapa nilai sel. Ini memberi Anda fungsionalitas fitur Teks ke Kolom tetapi melalui rumus
TEXTSPLIT ( B3 , " " )TEXTSPLIT di atas menemukan karakter spasi dalam kontak di sel B3. Semua karakter di sebelah kiri spasi diekstraksi sebagai satu item, seperti semua karakter di sebelah kanan
Hasilnya adalah larik dua item yang berisi nama depan diikuti dengan nama belakang
SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) )Fungsi COUNTA menghitung jumlah item array, yang untuk sebagian besar nama adalah dua
Fungsi SEQUENCE menggunakan hitungan tersebut untuk membuat larik dua item dari nomor berurutan. Inilah yang akan digunakan untuk membalik urutan nama
SORTBY ( TRANSPOSE ( TEXTSPLIT ( B3 , " " ) ) , SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) ) , -1 )Kedua array dipasok ke fungsi SORTBY yang mengatur ulang nama sebagai hasil dari nomor yang sesuai
LEN ( B3 ) - FIND ( " " , B3 )_0 memberlakukan pengurutan terbalik, secara efektif mengganti nama
TRANSPOSE diperlukan karena SORTBY mengharapkan larik nama diisi secara vertikal seperti larik angka
= TEXTJOIN ( ", " , TRUE , SORTBY ( TRANSPOSE ( TEXTSPLIT ( B3 , " " ) ) , SEQUENCE ( COUNTA ( TEXTSPLIT ( B3 , " " ) ) ) , -1 ) )Versi terakhir dari rumus di atas. Rekatkan versi ini ke bilah rumus Anda
Fungsi TEXTJOIN di atas mengubah susunan nama yang dialihkan menjadi satu nama lengkap, termasuk koma yang disematkan (FIND ( " " , B3 )6)
Satu-satunya langkah yang tersisa adalah menyalin formula ke kontak lain. Arahkan kursor ke sudut kanan bawah sel rumus sampai kursor Anda menjadi plus (FIND ( " " , B3 )7), lalu klik kiri dan tahan tombol mouse Anda sambil menyeret ke bawah
Anda sekarang memiliki semua nama kontak Anda dibalik dengan koma di antaranya
Tukar Nama Depan dan Belakang dengan Flash Fill
Fitur Isi Cepat adalah cara yang bagus untuk mengganti nama depan dan belakang tanpa harus menulis rumus yang rumit
Dengan memberikan beberapa contoh nama yang diganti, Isi Cepat dapat belajar dari contoh Anda dan secara otomatis mengganti nama yang tersisa
Mulai Flash Fill dengan terlebih dahulu memilih sel kosong di sebelah kanan kontak pertama Anda. Di sel itu, ketikkan nama kontak dengan nama depan dan belakang diganti. Lalu tekan Enter
Pastikan sel aktif masih dalam kolom contoh Anda
Buka tab pita Data dan klik Isian Cepat, atau cukup tekan Ctrl + E
Ajaibnya, Flash Fill mengenali bahwa Anda ingin nama depan dan belakang diganti dan akan menawarkan pratinjau hasilnya
Untuk menerima hasilnya, klik menu Opsi Isi Cepat di pojok kanan bawah sel Anda, lalu klik Terima saran
Anda semua selesai dan semua nama dibalik
Ganti Nama Depan dan Belakang dengan Power Query
Power Query adalah fitur Excel yang memungkinkan Anda melakukan transformasi kompleks pada data Anda
Untuk mengganti nama depan dan belakang, Anda dapat memberikan beberapa contoh Power Query dan setelah pola dikenali, Power Query akan menyelesaikan tugas untuk Anda dan membuat rumus yang diperlukan untuk transformasi
Untuk meluncurkan Power Query, pertama-tama pilih kolom kontak Anda, termasuk header Anda
Buka tab pita Data dan klik Dari Tabel/Rentang
Dari jendela Editor Power Query, buka tab Tambahkan Kolom, lalu klik Kolom Dari Contoh
Anda perlu memberikan contoh yang cukup untuk Power Query untuk mengenali pola pengalihan
Ketik contoh pertama Anda di baris pertama kolom kanan. Misalnya, jika kontak pertama Anda adalah "Trent Buckston", ketikkan "Buckston, Trent", seperti yang ditampilkan di atas
Tekan Enter setelah mengetik
📝 Catatan. Anda tidak perlu memberikan contoh untuk memulai dengan baris pertama. Anda dapat memberikan contoh di baris mana pun
Ketik contoh kedua di baris kedua untuk membantu Power Query lebih lanjut
Sekali lagi, tekan Enter setelah mengetik
Perhatikan di atas bagaimana Power Query menggunakan contoh Anda untuk menebak baris yang tersisa. Hasilnya tidak persis seperti yang Anda inginkan, jadi Anda perlu memberikan contoh tambahan
Setelah Anda memberikan contoh ketiga, Anda akhirnya akan melihat hasil yang diinginkan
Pilih header kolom Kustom dan ketikkan nama deskriptif seperti Switched, lalu tekan Enter dan klik tombol OK
Perhatikan di atas bahwa kolom Nama Lengkap asli masih muncul. Karena Anda sudah memiliki kolom Nama Lengkap di lembar Anda, Anda dapat menghapus kolom tersebut
Pilih kolom dan pergi ke tab Beranda lalu klik pada perintah Hapus Kolom
Tempatkan kolom Beralih ke lembar Anda dengan mengklik tarik-turun Tutup dan Muat, lalu Tutup & Muat Ke
Dari jendela Import Data, pilih opsi Table dan pilih Existing worksheet. Sorot konten kotak teksnya dan pilih sel kosong di lembar Anda. Sel ini akan menjadi baris pertama dari hasil Anda
Setelah selesai, klik tombol OK
Seperti yang Anda lihat di atas, kolom nama yang dialihkan sekarang muncul di lembar Anda
Ganti Nama Depan dan Belakang dengan Power Pivot
Power Pivot adalah add-in untuk Excel yang menambahkan kecerdasan bisnis ke sejumlah besar data
Dengan Power Pivot, Anda dapat menambahkan kolom terhitung dari nama yang dialihkan, lalu menampilkan kolom tersebut dengan tabel pivot
Secara default, Power Pivot dinonaktifkan di Excel. Tapi Anda bisa mengaktifkannya untuk menambahkan tab Power Pivot ke pita Excel Anda
Pilih data kontak Anda, buka tab pita Power Pivot dan klik Tambahkan ke Model Data
Data kontak Anda kini muncul sebagai kolom tabel di Power Pivot. Sangat membantu untuk memberi nama yang lebih deskriptif pada tabel ini dengan mengklik dua kali tab bawah dan menamainya ulang Nama
Tekan Enter setelah selesai
FIND ( " " , B3 )0Untuk membuat kolom nama yang dialihkan, pilih kolom berlabel Tambahkan kolom.
Rekatkan rumus di atas ke bilah rumus. Rumus ini melakukan tindakan yang sama dengan solusi rumus teks yang terlihat sebelumnya di posting ini
Perbedaan utamanya adalah Power Pivot menggunakan bahasa rumus yang disebut DAX untuk mereferensikan seluruh kolom sekaligus
Misalnya, rumus mereferensikan kolom Nama Lengkap dari tabel Nama menggunakan sintaks LEN ( B3 ) - FIND ( " " , B3 )3
Setelah Anda menekan Enter, semua nama Anda yang dialihkan muncul di kolom baru
Beri kolom baru Anda nama yang lebih deskriptif dengan mengklik dua kali tajuk kolom dan mengetik Beralih
Tekan Enter setelah selesai
Anda sekarang siap untuk menampilkan kolom baru di lembar Anda. Masuk ke tab Beranda dan klik PivotTable
Pilih tujuan untuk Tabel Pivot Anda. Dalam contoh di atas, lembar kerja baru akan dibuat
Klik tombol OK jika sudah siap
Di panel PivotTable Fields, telusuri tabel Names dan centang bidang Switched, sehingga bidang muncul di area Rows
Nama Anda yang dialihkan sekarang ada di lembar Anda di dalam tabel pivot
Ganti Nama Depan dan Belakang dengan VBA
Bahasa pemrograman VBA Excel menawarkan solusi yang sangat otomatis dan canggih untuk mengganti nama Anda
Melalui VBA, Anda dapat membuat kode yang berulang pada setiap kontak lalu memisahkan dan menggabungkan kembali nama mereka dalam urutan terbalik sebelum mengeluarkannya ke kolom baru
Untuk menambahkan kode tersebut ke buku kerja Anda, buka Editor VBA dengan menekan Alt + F11
Sebaiknya pisahkan kode kustom Anda dari buku kerja itu sendiri, jadi buat modul dengan memilih menu Sisipkan, lalu pilih Modul
FIND ( " " , B3 )_1Klik dua kali modul baru untuk membukanya, lalu tempelkan kode VBA di atas ke dalam modul
Saat dijalankan, kode di atas pertama-tama memastikan bahwa Anda hanya memilih satu kolom kontak di lembar Anda. Pilihan lainnya menyebabkan eksekusi diakhiri dengan notifikasi
Kode berputar melalui setiap sel yang dipilih dan memisahkan nama depan dan belakang. Pemisahan terjadi pada posisi karakter spasi melalui fungsi LEN ( B3 ) - FIND ( " " , B3 )4
Kedua nama ditempatkan dalam array yang disebut LEN ( B3 ) - FIND ( " " , B3 )5. Elemen array digabungkan kembali dalam urutan terbalik menggunakan ampersand (FIND ( " " , B3 )4) dan ditempatkan di sel yang berdekatan yang diwakili oleh LEN ( B3 ) - FIND ( " " , B3 )7
Kode VBA seperti ini sering disebut sebagai makro bila bisa dijalankan dari sheet Anda
Untuk menjalankan makro ini, kembali ke lembar Anda dan pastikan Anda telah memilih kolom kontak Anda
Pilih tab Lihat pita dan klik Makro
Dari dialog Makro, pilih makro SplitNames Anda dan klik tombol Jalankan
Saat proses selesai, Anda dapat melihat kolom yang berdekatan sekarang berisi kontak Anda dengan nama terbalik
Ganti Nama Depan dan Belakang dengan Skrip Office
Dengan hadirnya Microsoft 365 untuk web, bahasa skrip baru yang disebut Skrip Office tersedia untuk pengguna paket bisnis Excel
Anda dapat memanfaatkan Skrip Office untuk mengotomatiskan tugas seperti mengganti nama depan dan belakang
Untuk memulai skrip Anda sendiri, buka buku kerja Anda dari browser web Anda
Pilih tab pita Otomatiskan , lalu klik Skrip Baru
Jika Anda tidak melihat tab Automate, periksa lisensi Microsoft 365 Anda untuk memastikan bahwa Anda memiliki rencana bisnis yang diperlukan untuk mengakses Office Scripts dan telah diaktifkan di pengaturan admin Anda
FIND ( " " , B3 )_2Di panel Editor Kode yang muncul, ganti seluruh isinya dengan skrip di atas
📝 Catatan. Anda selalu membutuhkan fungsi LEN ( B3 ) - FIND ( " " , B3 )8 dalam skrip Anda karena itu adalah titik awal dari setiap skrip
Skrip ini pertama-tama memastikan bahwa Anda hanya memiliki satu kolom kontak yang dipilih, jika tidak, skrip akan dihentikan
Skrip berputar melalui setiap kontak. Nama depan dan belakang diekstraksi dengan mencari posisi karakter spasi
Kedua nama digabungkan kembali dalam urutan terbalik dengan koma (FIND ( " " , B3 )6) di antaranya. Hasilnya ditempatkan ke sel yang berdekatan sebelum memproses kontak berikutnya
Sebelum menjalankan skrip Anda, buka lembar Anda dan pilih kolom kontak Anda
Klik tombol Jalankan di panel Editor Kode untuk mengeksekusi kode
Setelah selesai, Anda dapat melihat kolom yang berdekatan sekarang memiliki kontak dengan nama mereka dialihkan
Kesimpulan
Di postingan ini, Anda telah melihat banyak cara untuk mengganti nama depan dan belakang di Excel
Fitur Teks ke Kolom menawarkan pengalaman yang bersih dan interaktif, sedangkan metode rumus teks mendapat manfaat dari langkah yang lebih sedikit dan hasilnya akan diperbarui secara dinamis jika nama sumber berubah
Rumus yang melibatkan TEXTSPLIT menghadapkan Anda pada manfaat dari fungsi larik dinamis Excel. Namun perlu diingat tidak semua pengguna akan memiliki fungsi ini
Fitur Isi Cepat sangat bagus untuk tugas berpola seperti mengganti nama, tetapi mungkin gagal saat data Anda diformat secara tidak konsisten
Untuk sumber data yang lebih besar, Anda dapat memilih Power Query atau Power Pivot. Power Query memerlukan lebih sedikit pengetahuan rumus dengan fitur kolom dengan contoh yang canggih
Jika tidak, Anda memiliki VBA dan Skrip Office sebagai opsi pengkodean yang fleksibel tergantung pada apakah Anda bekerja dengan aplikasi desktop atau aplikasi online