Cara memisahkan gelar di excel

By On Wednesday, September 28th, 2022 Categories : Tanya Jawab

Cara memisahkan gelar di excel – Apa khabar om dan tante semua, Makasih banyak dah mau berkunjung ke halaman blog trendwisata.com ini. Sekarang, kita di blog Trend Wisata dot com mau membagikan ulasan yang menarik yang mengulas Cara memisahkan gelar di excel. Ini dia bapak dan ibu menyimaknya dibawah ini:

Cara Memisahkan Gelar dan Nama – Menulis gelar dan nama memang biasanya langsung disatukan atau digabungkan.

Tapi jika teks gelar dan nama tersebut kita olah datanya dalam Excel maka kita bisa memisahkan gelar dan nama tersebut secara otomatis.

Tentu kita akan menggunakan rumus – rumus yang sudah disediakan oleh Microsoft Excel.

Untuk cara dan rumus apa saja yang
digunakan mari kita bahas bersama – sama dalam artikel ini sampai dengan selesai.

Cara Memisahkan Gelar dan Nama Dalam Excel

Dalam artikel sebelumnya kita juga sudah membahas tentang cara memisahkan tempat dan tanggal lahir.

Baca Juga : Cara Memisahkan Tempat Tanggal Lahir Kedalam Cell Kota dan Tanggal Dalam
Excel

Pada prinsipnya untuk pola pemisahan pada pembahasan ini cukup mirip dengan cara dalam pembahasan tersebut.

Hanya saja pemisah teks yang kita gunakan kali ini berbeda bukan koma tetapi titik.

Selanjutnya untuk rumus yang akan digunakan yaitu LEFT, FIND, RIGHT dan LEN.

Sebaiknya silahkan pelajari rumus – rumus tersebut diatas supaya tidak susah memahami gabungan rumus yang akan kita gunakan dalam
pembahasan ini.

Nanti kita akan pisah gelar dan nama tersebut kedalam dua kolom yaitu Gelar dan Nama.

Tentu rumus yang digunakan didalam dua kolom tersebut akan beda meskipun sama – sama memisahkan teks.

Contoh Memisahkan Gelar dan Nama

Setelah mengetahui rumus apa saja yang digunakan selanjutnya mari kita lihat contoh penggunaannya.

Silahkan perhatikan gambar berikut ini :

Dalam contoh gambar tersebut dalam kolom A terdapat teks nama dan juga gelar atau teks lain yang akan kita pisahkan.

Selanjutnya teks yang dipisahkan sudah kita simpan masing – masing kedalam kolom B dan kolom C.

Adapun rumus yang digunakan dalam kolom B Cell B4 adalah sebagai berikut :

=LEFT(A4;FIND(“.”;A4)-1)

Selanjutnya rumus yang
digunakan untuk memisahkan Nama pada kolom C Cell C4 adalah sebagai berikut :

=RIGHT(A4;(LEN(A4)-FIND(“.”;A4)))

Dari rumus tersebut diatas terlihat bahwa Nama memang sudah bisa dipisahkan meskipun jumlah huruf atau teks pada nama berbeda – beda.

Begitupun dengan gelar, sudah bisa kita pisahkan meskipun memang gelar ini jumlah hurufnya bisa berbeda – beda.

Pada dasarnya pengambilan huruf dengan rumus
LEFT dan RIGHT memang terbatas pada jumlah huruf tertentu saja.

Untuk mengatasi hal tersebut kita gabungkan beberapa rumus lainnya supaya angka jumlah huruf pada pengambilan teks tersebut bisa berubah – ubah sesuai dengan jumlah huruf pada nama atau gelar tersebut.

Itulah jawabannya kenapa pada dua rumus tersebut tidak secara langsung dimasukan berapa huruf yang akan diambil dari teks pada kolom A tersebut.

Itulah pembahasan kita kali ini
tentang cara memisahkan gelar dan nama dan semoga artikel ini bermanfaat untuk semua pembaca.

Rumus excel memisahkan nama depan, tengah dan belakang dengan microsoft excel memakai rumus-rumus gabungan (FIND, LEFT, RIGHT, LEN, dll)

Kali ini kita akan belajar menggunakan rumus excel untuk memisahkan kata/kalimat teks atau lebih tepatnya memisahkan nama depan, tengah dan
belakang pada sebuah teks nama lengkap di Microsoft Excel.

  • Memisahkan Nama Lengkap Dengan Fitur “Text To Columns” Excel
  • Mengambil Nama Depan
  • Mengambil Nama Belakang
    1. Nama Belakang Adalah Kata Setelah Spasi Pertama Dan Kosong Jika Nama Lengkap Hanya Terdiri Dari Satu Kosa Kata
    2. Nama Belakang Adalah Kata Setelah Spasi Pertama Dan Jika Nama Lengkap Hanya Terdiri Dari Satu Kosa Kata Maka Nama Belakang Sama Dengan Nama Depan
    3. Nama Belakang Hanya Kata Setelah Spasi Terakhir
  • Mengambil Nama Tengah

Untuk memisahkan kata yang dipisahkan oleh spasi menjadi beberapa kolom sebenarnya relatif mudah. Excel sudah menyediakan sebuah menu khusus untuk melakukan hal ini, yakni dengan menggukan menu “Text to Columns”
yang sudah dijelaskan pada halaman lain.

Fitur “Text to Columns” bisa juga kita manfaatkan untuk memisahkan nama depan, tengah dan belakang secara manual. Namun terkadang kita membutuhkan rumus-rumus excel khusus untuk memenuhi kebutuhan pemisahan nama yang tidak bisa kita lakukan dengan fitur “Text to Columns“.

Sebelum membahas pemisahan nama dengan rumus excel akan saya bahas sedikit tentang penggunaan fitur “Text to Column” ini, baru setelahnya akan saya bahas
tentang cara memisahkan nama depan, tengah dan belakang menggunakan rumus-rumus microsoft excel.

*File pelengkap rumus excel tentang memisahkan nama ini bisa anda download pada bagian akhir artikel ini.

A. Memisahkan Nama Dengan Fitur Text To Columns

Ikuti langkah-langkah berikut untuk menggunakan fitur Text to Column untuk memisahkan nama berdasarkan spasi.

  1. Tandai atau seleksi nama-nama yang akan dipisah-pisahkan
  2. Pilih menu
    TABData–Text to Columns

  3. Pada option box pilih
    Delimited kemudian klik Next

  4. Pada options delimiter
    pastikan Space tercentang kemudian klik Next

  5. Beikutnya muncul option
    data format untuk masing-masing kolom. Jika perlu tentukan format pada masing-masing kolom. Atau biarkan saja.
  6. Klik Finish. Selesai

Setelah
melakukan langkah-langkah diatas, teks sudah terpisah sesuai spasi pada teks sebelumnya.

Hasil pemisahan teks diatas adalah sebagai berikut :

Jika anda mempraktekkanya tentu
anda tahu bahwa dengan menggunakan fitur text to columns, teks awal yang dipisah tersebut menjadi berubah, yakni terpisah-pisah menjadi beberapa kolom. Dalam kasus tertentu, tentunya bukan yang seperti ini yang kita harapkan dalam pemisahan nama. Untuk itu simak cara pemishan nama depan, tengah dan nama belakang berikutnya.

BACA PELAN-PELAN CARA PEMISAHAN NAMA DENGAN RUMUS EXCEL DIBAWAH INI. JANGAN TERGESA-GESA AGAR TIDAK TERSESAT 🙂

B. Rumus Mengambil Nama Depan

Biasanya
nama depan adalah nama sebelum spasi pertama. Untuk mengambil nama depan maka bisa kita lakukan dengan menggunakan fungsi LEFT dengan menggunakan fungsi FIND untuk mencari posisi spasi (” “) pertama dari kiri. Hasil pencarian posisi ini digunakan untuk mengisi jumlah
karakter yang diambil dengan fungsi LEFT.

Rumus Excelnya adalah sebagai berikut:

=LEFT(Nama_Lengkap;FIND(" ";Nama_Lengkap;1)-1)

Selain menggunakan fungsi FIND untuk mencari posisi spasi pertama, anda juga bisa menggunakan fungsi SEARCH. Untuk lebih jelasnya perhatikan contoh gambar berikut:

Kenapa jumlah karakter atau
posisi spasi harus minus satu (-1)?

Rumus Excel FIND(” “;Nama_Lengkap;1) digunakan untuk mencari posisi spasi pertama, sehingga jika tidak kita kurangi satu maka nama depan yang dihasilkan akan mengandung spasi yang secara kasat mata tidak terlihat di akhir teks nama depan tersebut.

Cara lain agar spasi di akhir teks nama depan tidak ikut tertulis adalah dengan menggunakan
fungsi TRIM, sehingga rumus excelnya menjadi:

=TRIM(LEFT(Nama_Lengkap;FIND(" ";Nama_Lengkap;1)))

Contoh penggunaannya seperti gambar berikut:

Lebih tepat
menggunakan FIND atau SEARCH? baca penjelasan kedua fungsi ini pada artikel berikut: “Beda Fungsi FIND dan SEARCH”.

Loh… kog hasilnya ada yang mengandung pesan error #VALUE ?

Seperti yang terlihat dalam kedua contoh diatas untuk nama yang hanya terdiri dari satu kosa kata atau tidak mengandung spasi hasil pengambilan nama depan adalah
sebuah pesan kesalahan/error (#VALUE). Hal ini disebabkan rumus yang kita jalankan tidak dapat menemukan spasi (” “) pada nama lengkap.

Untuk mengatasi ini gunakan fungsi IFERROR. sehingga rumus excelnya akan menjadi:

=IFERROR(LEFT(Nama_Lengkap;FIND(" "; Nama_Lengkap;1)-1); Nama_Lengkap)

atau jika menggunakan fungsi TRIM:

=IFERROR(TRIM(LEFT(Nama_Lengkap;FIND(" "; Nama_Lengkap;1))); Nama_Lengkap)

Setalah menggunakan rumus excel tersebut hasilnya akan seperti gambar di bawah ini:

Sebenarnya masih ada
sedikit pertanyaan tentang nama depan ini. Bagaimana jika nama depan mengandung gelar? misal : Dr. Setya Budi Pranomo. Jika menggunakan rumus-rumus excel diatas tentunya hasil nama depan adalah “Dr.” padahal yang kita harapkan nama depan adalah “Dr. Setya ” atau “Setya” saja.

Untuk kasus nama dengan gelar akan kita bahas lain waktu. Sekarang kita lanjut dulu cara mendapatkan nama belakang.

C. Rumus Mengambil Nama Belakang

Cara mengambil nama tengah ini
sedikit rumit jadi harap sabar membaca step by stepnya.

Sebelum menetapkan rumus excel mana yang harus digunakan, kita tentukan dulu kriteria nama belakang itu seperti apa jika nama yang akan kita pisahkan tidak terdiri dua kosa kata.

Maksud saya begini, jika namanya hanya terdiri 1 kata misal “Abdurrahman”, maka nama belakangnya kosong atau Abdurrahman (sama dengan nama depannya). Jika namanya terdiri dari 3 kosa kata misal “Bhinneka Tunggal Ika” maka
nama belakangnya “Ika” saja atau “Tunggal Ika”.

Pilihan diatas tentunya terserah mau anda seperti apa. Biasanya pemisahan nama belakang ini juga dipengaruhi apakah selain memisahkan nama depan dan belakang anda juga perlu memisahkan nama tengah atau cukup nama depan dan belakang saja.

Jika sudah mengambil keputusan model nama belakang seperti apa yang anda gunakan lanjutkan membaca cara mengambil nama belakang di excel berikut ini.

1. Nama Belakang
Adalah Kata setelah Spasi Pertama dan Kosong Jika Hanya Terdiri Dari Satu Kosa Kata

Untuk model pertama ini, nama belakang adalah selain nama depan. Jika hanya ada nama depan (nama depan hanya terdiri satu kosa kata) maka nama belakangnya kosong. Rumus excel yang kita gunakan untuk model nama belakang ini adalah:

=IFERROR(RIGHT(Nama_Lengkap;LEN(Nama_Lengkap)-FIND(" ";Nama_Lengkap;1));"")

Rumus diatas mengambil karakter Nama_Lengkap dari sebelah kanan. Kebalikan dari cara mengambil nama depan sebelumnya.

RIGHT(Nama_Lengkap; ...;1)

Jumlah karakter yang diambil
adalah selisih antara jumlah karakter Nama_Lengkap dengan posisi spasi pertama.

LEN(Nama_Lengkap)-FIND(" ";Nama_Lengkap;1)

Jika tidak ditemukan spasi, maka hasilnya kosong (“”)

IFERROR( ... ;"")

Contoh mengambil nama belakang dengan rumus ini adalah :

Selain
menggunakan fungsi RIGHT anda juga bisa menggunakan fungsi MID untuk memisahkan nama belakang model ini. Rumusnya adalah:

=MID(Nama_Lengkap;FIND(" ";Nama_Lengkap;1)+1;LEN(Nama_Lengkap)-FIND(" ";Nama_Lengkap;1))

Agar bisa diterapkan juga untuk nama yang hanya terdiri dari satu kosa kata tambahkan IFERROR.

=IFERROR(MID(Nama_Lengkap;FIND(" ";Nama_Lengkap;1)+1;LEN(Nama_Lengkap)-FIND(" ";Nama_Lengkap;1));"")

Contoh memisahkan nama belakang dengan rumus ini sebagai berikut:

2. Nama Belakang Adalah Kata setelah Spasi Pertama dan Jika Hanya Terdiri Dari Satu Kosa Kata Maka Nama Belakang Sama dengan Nama Depan

Untuk model kedua, nama belakang adalah selain nama depan. Jika hanya ada nama depan (nama depan hanya terdiri satu kosa kata) maka nama belakangnya sama dengan nama depan. Rumus excel yang kita gunakan untuk model ini mirip dengan model pertama hanya beda di bagian terakhir:

=IFERROR(RIGHT(Nama_Lengkap;LEN(Nama_Lengkap)-FIND(" ";Nama_Lengkap;1));Nama_Lengkap)

Contoh penerapannya sebagai berikut:

Bisa
menggunakan fungsi MID seperti model pertama rumus excelnya adalah:

=IFERROR(MID(Nama_Lengkap;FIND(" ";Nama_Lengkap;1)+1;LEN(Nama_Lengkap)-FIND(" ";Nama_Lengkap;1));Nama_Lengkap)

Contoh penggunaannya:

Perhatikan
hasil untuk nama Abdurrahman untuk membedakan hasil rumus model kedua ini dengan rumus model pertama sebelumnya.

3. Nama Belakang Hanya Kata Setelah Spasi Terakhir

Model ketiga ini biasanya digunakan jika selain nama depan dan belakang anda juga perlu memisahkan nama tengah.

Untuk Jenis nama belakang ini, misal nama lengkapnya adalah “Bhinneka Tunggal Ika” maka nama belakangnya adalah “Ika”. Rumus Excelnya sebagai berikut:

=RIGHT(Nama_Lengkap;LEN(Nama_Lengkap)-FIND("*";SUBSTITUTE(Nama_Lengkap;" ";"*";LEN(Nama_Lengkap)-LEN(SUBSTITUTE(Nama_Lengkap;" ";"")))))

Agar
tidak menghasilkan nilai error #VALUE jika ada nama yang hanya terdiri dari satu kosa kata tambahkan IFERROR seperti model-model sebelumnya, Sehingga rumus excelnya menjadi:

=IFERROR(RIGHT(Nama_Lengkap;LEN(Nama_Lengkap)-FIND("*";SUBSTITUTE(Nama_Lengkap;" ";"*";LEN(Nama_Lengkap)-LEN(SUBSTITUTE(Nama_Lengkap;" ";"")))));"")

Contoh penggunaan rumus ini sebagai berikut:

Untuk nama
belakang model ini rumusnya memang sedikit rumit, sebab nama lengkap seseorang saat ini kebanyakan terdiri lebih dari dua suku kata belum lagi kalau ada titlenya berderet-deret.

Cara kerja rumus diatas jika dijabarkan lebih lanjut adalah sebagai berikut:

  1. Mengambil beberapa karakter dari kanan:
    RIGHT(Nama_Lengkap;Banyak_Karakter)
  2. Banyak Karakter pada langkah nomor 1 adalah jumlah karakter Nama_Lengkap dikurangi posisi tanda bintang (*).
  3. Untuk mencari banyak karakter Nama_Lengkap adalah dengan
    menggunakan fungsi LEN

    LEN(Nama_Lengkap)
  4. Sebelum melakukan pencarian tanda bintang, ganti spasi terakhir Nama_Lengkap dengan tanda bintang (*). Untuk melakukan hal ini kita menggunakan fungsi SUBSTITUTE
    SUBSTITUTE(Nama_Lengkap;" ";"*";Posisi_Spasi_Terakhir))
  5. Untuk mengetahui posisi spasi terakhir kurangi jumlah karakter
    Nama_Lengkap dengan Nama_Lengkap tanpa spasi

    LEN(Nama_Lengkap)-LEN(SUBSTITUTE(Nama_Lengkap;" ";"")
  6. Langkah 4 dan 5 jika digabung menjadi:
    SUBSTITUTE(Nama_Lengkap;" ";"*";LEN(Nama_Lengkap)-LEN(SUBSTITUTE(Nama_Lengkap;" ";"")))
  7. 7. Untuk mencari posisi tanda bintang (*)
    FIND("*";Rumus_Nomor_6)

Garis besar langkah-langkah diatas adalah ganti spasi terakhir dengan tanda bintang dan lakukan pengambilan sejumlah karakter setelah tanda bintang tersebut.

Rumus diatas menghasilkan teks kosong (“”) jika nama hanya terdiri dari satu kosa kata saja. Jika anda menghendaki nama belakang sama dengan nama depan untuk
Nama_lengkap yang terdiri dari satu kosa kata, ganti bagian terakhir rumus diatas dengan referensi nama_lengkap, sehingga rumus excelnya menjadi:

=IFERROR(RIGHT(Nama_Lengkap;LEN(Nama_Lengkap)-FIND("*";SUBSTITUTE(Nama_Lengkap;" ";"*";LEN(Nama_Lengkap)-LEN(SUBSTITUTE(Nama_Lengkap;" ";"")))));Nama_Lengkap)

Perhatikan nama Abdurrahman pada contoh berikut dan bandingkan dengan hasil sebelumnya:

Masih
punya masalah dengan nama cara memisahkan nama belakang? sampaikan di kolom komentar. Sekarang kita lanjut dulu tentang cara memisahkan atau mengambil nama tengah.

C. Rumus Mengambil Nama Tengah

Setelah memahami cara memisahkan nama depan dan nama belakang dengan rumus-rumus excel diatas berikutnya kita akan belajar tenatng cara memisahkan nama tengah.

Karena nama tengah pastinya ada di bagian tengah sebuah nama, fungsi utama yang kita gunakan adalah
fungsi MID. Yakni mengambil beberapa karakter teks dimulai dari posisi spasi pertama sebanyak jumlah antara posisi spasi pertama dan jumlah karakter setelah spasi terakhir.

Secara lebih sederhana nama tengah adalah selain nama depan dan nama belakang. 😀

Rumus Excel yang kita gunakan untuk megambil nama tengah ini adalah:

=MID(B2;FIND(" ";Nama_Lengkap;1)+1;FIND(" ";Nama_Lengkap;1)+LEN(RIGHT(Nama_Lengkap;LEN(Nama_Lengkap)-FIND("*";SUBSTITUTE(Nama_Lengkap;" ";"*";LEN(Nama_Lengkap)-LEN(SUBSTITUTE(Nama_Lengkap;" ";"")))))))

Untuk menghindari
error pada nama yang hanya terdiri dari satu kosa kata gunakan IFERROR seperti sebelumnya:

=IFERROR(MID(Nama_Lengkap;FIND(" ";Nama_Lengkap;1)+1;FIND(" ";Nama_Lengkap;1)+LEN(RIGHT(Nama_Lengkap;LEN(Nama_Lengkap)-FIND("*";SUBSTITUTE(Nama_Lengkap;" ";"*";LEN(Nama_Lengkap)-LEN(SUBSTITUTE(Nama_Lengkap;" ";"")))))));"")

Hasilnya akan seperti pada gambar dibawah ini:

Sebagai catatan bahawa rumus pengambilan atau pemisahan nama tengah diatas akan menghasilkan teks kosong (“”) untuk nama lengkap yang hasny terdiri dari satu atau dua kosa kata.

Jika anda perhatikan lebih teliti sebenarnya rumus-rumus diatas
juga mengikut sertakan rumus-rumus untuk pemisahan nama depan dan belakang yang sudah kita bahas di bagaian atas.

Itulah salah satu alasan saya kenapa nama tengah ini saya bahas setelah membahas nama depan dan nama belakang terlebih dahulu. Sehingga pada prakteknya anda tidak harus menggunakan rumus diatas, namun bisa anda sederhanakan lagi.

Caranya bagaimana? Silahkan uji kreatifitas anda. Sebagai pelengkap silahakan download file latihan dibawah ini:

Dropbox
GDrive

*Jika link mati / tidak dapat diakses silahkan lapor via kontak yang tersedia

Setelah memahami cara mengambil nama depan, tengah dan belakang,
cobalah untuk membuat sebuah tabel baru dengan kolom nomor urut, nama lengkap. nama depan, nama tengah dan nama belakang.

Kemudian isi kolom nomor urut dengan nomor urut. Kemudian isi baris-baris pada nama lengkap dengan nama-nama terserah anda. Usahakan jumlah kata pada nama lengkap bervariasi dan cobalah gunakan rumus-rumus excel diatas untuk memisahkna nama-nama tersebut.

Semoga berhasil. 😀

Bagaimana cara memisahkan kata di excel?

Cobalah!.

Pilih sel atau kolom yang berisi teks yang ingin dipisahkan..

Pilih Data > teks ke kolom..

Di panduan konversi teks ke kolom, pilih terbatas > berikutnya..

Pilih pembatas untuk data Anda. … .

Pilih Berikutnya..

Pilih tujuan dalam lembar kerja Anda yang akan menjadi tempat Anda ingin memisahkan data..

Pilih Selesai..

Bagaimana formula untuk vlookup?

Dalam bentuknya yang paling sederhana, fungsi VLOOKUP mengatakan: =VLOOKUP(Apa yang ingin Anda cari, di mana Anda ingin mencarinya, nomor kolom dalam rentang yang berisi nilai yang akan dikembalikan, mengembalikan hasil yang Mendekati atau Persis – ditunjukkan sebagai 1/TRUE, atau 0/FALSE).

Rumus apa saja yang ada di excel?

Bagi kamu yang ingin mengolah data dengan mudah, berikut adalah rumus excel lengkap yang dapat kamu gunakan dalam pekerjaan..

SUM. Jika mau bertanya-tanya, “apa formula untuk menemukan jumlah total atau cara menghitung total di excel?” … .

AVERAGE. … .

COUNT. … .

COUNTBLANK. … .

COUNTA. … .

SINGLE IF. … .

MULTI IF. … .

COUNTIF..

Bagaimana cara menghapus spasi di excel?

Gunakan fungsi Find & Replace untuk menghapus semua spasi dari sel. Selain di atas PENGGANTI fungsi, fitur Find & Replace juga dapat membantu untuk menghapus semua spasi dari sel. 1. Pilih sel yang berisi spasi yang akan Anda hapus, tekan Ctrl + H kunci untuk membuka Cari dan Ganti kotak dialog.

Cara memisahkan gelar di excel | admin | 4.5
shopee flash sale gratis ongkir
x