RumusHitung.com – Halo guys, bagaimana kabarnya? Semoga masih semangat yaa.. Kali ini rumushitung akan melanjutkan pembahasan mengenai rumus Excel. Pada pembahasan sebelumnya, sobat telah mempelajari tentang gabungan rumus Average pada Excel, pengelompokan data (grouping), gabungan rumus Rank pada Excel, seri tanggal (date series) pada Excel, dan gabungan rumus Random pada Excel. Selanjutnya, kita akan bahas mengenai nama dalam perumusannya pada Excel.
Ada 7 contoh nama dalam rumus Excel, antara lain :
1) Dapatkan nama depan dari nama lengkap
2) Dapatkan nama depan dari nama dengan koma
3) Dapatkan nama belakang dari nama lengkap
4) Dapatkan nama belakang dari nama dengan koma
5) Dapatkan nama tengah dari nama lengkap
6) Penggabungan nama depan dan belakang
7) Masukkan nama ke dalam kasus yang tepat
Contents
Dapatkan Nama Depan dari Nama Lengkap
Jika ingin mengekstrak nama depan dari nama lengkap, gunakan fungsi FIND dan LEFT. Dalam bentuk rumus umum di atas, nama adalah nama lengkap dengan spasi yang memisahkan nama depan dari bagian nama lainnya.
Pada contoh, sel aktif berisi rumus seperti :
=LEFT(B3;FIND(“ “;B3)-1)
Fungsi FIND menemukan karakter spasi pertama (“ “) dalam nama dan menghasilkan posisi spasi tersebut di nama lengkap. Angka ini digunakan oleh fungsi LEFT sebagai jumlah total karakter yang harus diekstraksi pada langkah berikutnya di bawah. Pada contoh, spasi pertama berada di posisi 6 minus 1 sama dengan 5 karakter yang akan diambil.
Fungsi LEFT mengekstrak karakter dari nama lengkap yang dimulai dari kiri dan berlanjut hingga jumlah karakter yang ditentukan pada langkah sebelumnya seperti di atas. Pada contoh, 5 adalah jumlah total karakter, jadi hasilnya adalah “Restu”.
Catatan : rumus ini tidak memperhitungkan gelar (Mr., Ms., dll) pada nama lengkap. Jika terdapat, harus dihapus terlebih dahulu.
Dapatkan Nama Depan dari Nama dengan Koma
Untuk mengekstrak nama depan dari nama lengkap dalam format “Belakang, Depan”, gunakan rumus yang menggunakan fungsi RIGHT, LEN, dan FIND. Dalam bentuk rumus umum di atas, nama adalah nama lengkap dalam format :
BELAKANG, DEPAN
Puspita, Reny
Wanastiti, Rizky
Putri, Shinta
Koma dan spasi memisahkan nama belakang dari nama depan.
Pada contoh, sel aktif berisi rumus seperti :
=RIGHT(B3;LEN(B3)-FIND(“; “;B3)-1)
Pada tingkat tinggi, rumus ini menggunakan RIGHT untuk mengekstrak karakter dari sisi kanan nama. Untuk mengetahui jumlah karakter yang perlu diekstraksi untuk mendapatkan nama depan, rumus menggunakan fungsi FIND untuk menemukan posisi “,” pada nama :
FIND(“; “;B3) / / posisi koma
FIND menghasilkan posisi koma dan spasi sebagai angka. Jumlah ini kemudian dikurangi dari total panjang nama :
LEN(B3)-FIND(“; “;B3) / / panjang nama depan + 1
Hasilnya adalah panjang nama depan, ditambah satu karakter tambahan, karena koma. Untuk memperoleh panjang sebenarnya, dikurangi 1 :
LEN(B3)-FIND(“; “;B3)-1 / / panjang nama depan
Karena namanya dalam urutan terbalik (BELAKANG, DEPAN), fungsi RIGHT bisa mengekstrak panjang nama depan.
Misalnya, namanya adalah “Restu, Adi”, posisi koma adalah 6. Jadi, rumus bagian dalam disederhanakan menjadi :
10-6-1=3 / / panjang nama depan
Kemudian :
RIGHT(“Restu;Adi”;3) / / “Adi”
Catatan : rumus ini hanya akan berfungsi dengan nama dalam format Belakang, Depan, dipisahkan dengan koma dan spasi.
Dapatkan Nama Belakang dari Nama Lengkap
Jika ingin mengekstrak nama belakang dari nama lengkap, lakukanlah dengan rumus yang agak rumit ini yang menggunakan beberapa fungsi. Dalam bentuk rumus umum di atas, nama adalah nama lengkap, dengan spasi yang memisahkan nama depan dari bagian nama lainnya.
Pada contoh, sel aktif berisi rumus seperti :
=RIGHT(B3;LEN(B3)-FIND(“*”;SUBSTITUTE(B3;” “;”*”;LEN(B3)-LEN(SUBSTITUTE(B3;” “;””)))))
Dapatkan Nama Belakang dari Nama dengan Koma
Jika ingin mengekstrak nama belakang dari nama lengkap dalam format Belakang, Depan, lakukan dengan rumus yang menggunakan fungsi LEFT dan FIND. Rumus berfungsi dengan nama dalam format ini, di mana koma dan spasi memisahkan nama belakang dari nama depan :
BELAKANG, DEPAN
Puspita, Reny
Wanastiti, Rizky
Putri, Shinta
Pada contoh yang ditunjukkan, sel aktif berisi rumus :
=LEFT(B3;FIND(“; ”;B3)-1)
Pada tingkat tinggi, rumus ini menggunakan LEFT untuk mengekstrak karakter dari sisi kiri nama. Untuk mengetahui jumlah karakter yang perlu diekstraksi untuk mendapatkan nama belakang, rumus menggunakan fungsi FIND untuk menemukan posisi “,” dalam nama :
FIND(“;”;B3) / / posisi koma
Koma sebenarnya adalah salah satu karakter setelah akhir nama belakang, jadi untuk memperoleh panjang sebenarnya dari nama belakang, 1 harus dikurangi :
FIND(“;”;B3)-1 / / panjang nama belakang
Karena utaamanya dalam urutan terbalik (BELAKANG, DEPAN), fungsi LEFT cukup mengekstrak nama belakang langsung dari kiri.
Misalnya, namanya “Restu; Adi”, posisi koma adalah 6. Jadi, rumusnya disederhanakan menjadi :
6-1=5 / / panjang nama belakang
Kemudian :
LEFT(“Restu;Adi”;5) / / “Restu”
Catatan : rumus ini hanya akan berfungsi dengan nama dalam format Belakang, Depan, dipisahkan dengan koma dan spasi.
Dapatkan Nama Tengah dari Nama Lengkap
Jika ingin memperoleh nama tengah dari nama lengkap, dan sudah mempunyai nama depan dan nama belakang di sel terpisah, bisa menggunakan rumus yang mengekstrak nama tengah menggunakan fungsi MID dan LEN, dengan bantuan fungsi TRIM.
Catatan : ini adalah rumus yang cukup ceroboh, namun akan berfungsi dalam banyak situasi karena TRIM membersihkan spasi ekstra, termasuk kasus di mana tidak ada nama tengah. Ini tidak akan berfungsi jika nama berisi judul atau sufiks yang muncul sebelum nama depan atau setelah nama belakang.
Dengan asumsi memiliki nama lengkap di kolom B, nama depan di kolom C, dan nama belakang di kolom D, kemudian gunakan rumus yang terlihat seperti :
=TRIM(MID(B3;LEN(C3)+1;LEN(B3)-LEN(C3&D3)))
Pada intinya, fungsi MID mengekstrak teks dari nama lengkap mulai dari 1 karakter setelah panjang nama depan. Total karakter yang diekstrak sama dengan panjang nama lengkap dikurangi panjang nama depan dan nama belakang disatukan.
Secara desain, rumus ini mengekstrak semua teks antara nama depan dan nama belakang, termasuk karakter spasi ekstra, dan kemudian mengandalkan kekuatan kasar pada fungsi TRIM untuk membersihkan semuanya pada akhirnya :
- Bila ada nama tengah, MID memperoleh nama tengah (dengan spasi dikedua sisinya) dan TRIM menghapus spasi ekstra.
- Jika ada lebih dari satu nama tengah, MID memperoleh semua nama tengah (dengan spasi dikedua sisinya) dan memotong strip karakter spasi ekstra.
- Jika tidak ada nama tengah, MID menghasilkan karakter spasi, yang dihapus oleh TRIM, tidak menyisakan apapun.
Penggabungan Nama Depan dan Nama Belakang
Jika ingin menggabungkan nama depan dan nama belakang yang terpisah menjadi satu nama lengkap, lakukan dengan menggunakan penggabungan menggunakan karakter (&). Dalam versi umum rumus di atas, nama depan adalah nama depan, nama belakang adalah nama belakang.
Pada contoh, sel aktif berisi rumus :
=B3&” “&C3
Karakter (&) adalah operator khusus di Excel yang digunakan untuk penggabungan. Penggabungan hanyalah kata mewah untuk “bergabung”.
Pada rumus, teks di B3 “Restu” digabungkan dengan karakter spasi “ “ dan kemudian ke teks pada sel C3, “Agil”.
Setiap kali menggunakan penggabungan dengan teks literal yang dimasukkan langsung di dalam rumus, pastikan untuk mengapit teks dalam tanda kutip ganda (“…“). Karakter (&) tidak pernah diapit yanda petik kecuali kalau karakter (&) muncul di hasil rumus sebagai teks literal.
Dengan CONCATENATE
Bisa juga menggunakan fungsi CONCATENATE untuk menggabungkan teks. Untuk contoh ini, rumus seperti :
=CONCATENATE(B3;” “;C3)
Fungsi CONCATENATE menjaga segala sesuatu tetap rapi, namun simbol dan membuat rumus yang lebih pendek dan fleksibel.
Masukkan Nama ke dalam Kasus yang Tepat
Jika ingin membersihkan nama yang tidak dalam kasua yang tepat, gunakan rumus sederhana berdasarkan fungsi PROPER.
Pada contoh rumus di sel C3 :
=PROPER(B3)
Fungsi PROPER secara otomatis memformat teks sehingga semua kata menggunakan huruf besar. Pada saat yang sama, ini menurunkan semua teks lainnya.
Jika ingin menghaspus juga spasi ekstra pada nama, bisa membungkus PROPER di fungsi TRIM :
TRIM(PROPER(nama))
Ini akan menghapus spasi di depan dan di belakang, dan mengubah beberapa spasi menjadi satu spasi.
Demikian penjelasan mengenai nama dalam rumus Excel, semoga bermanfaat. Sekian terima kasih.