Formula generik
=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Ringkasan
Sekiranya anda perlu mengeluarkan nama belakang dari nama penuh, anda boleh melakukannya dengan formula yang agak rumit ini yang menggunakan beberapa fungsi. Dalam bentuk rumus generik (di atas), nama adalah nama penuh, dengan ruang yang memisahkan nama pertama dari bahagian nama yang lain.
Dalam contohnya, sel aktif mengandungi formula ini:
=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))
Penjelasan
Pada intinya, formula ini menggunakan fungsi KANAN untuk mengekstrak watak bermula dari kanan. Fungsi lain yang membentuk bahagian rumus formula ini hanya melakukan satu perkara: mereka mengira berapa watak yang perlu diekstrak.
Pada tahap tinggi, formula menggantikan ruang terakhir dalam nama dengan tanda bintang "*" dan kemudian menggunakan FIND untuk menentukan kedudukan asterisk dalam nama. Posisi digunakan untuk mengetahui berapa banyak watak yang akan diekstrak dengan KANAN.
Bagaimana fungsi hanya menggantikan ruang terakhir? Ini adalah bahagian yang bijak.
Tegas, penjelasannya sedikit teknikal.
Kunci formula ini ialah:
SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))
Yang mana sebenarnya penggantian ruang terakhir dengan "*".
SUBSTITUTE mempunyai argumen keempat (opsional) yang menentukan "contoh" teks temuan mana yang harus diganti. Sekiranya tidak ada yang diberikan untuk argumen ini, semua contoh diganti. Namun, jika, katakan nombor 2 diberikan, hanya contoh kedua yang diganti. Dalam coretan di atas, contoh dikira menggunakan SUBSTITUT kedua:
LEN(B4)-LEN(SUBSTITUTE(B4," ",""))
Di sini, panjang nama tanpa spasi dikurangkan dari panjang nama yang sebenarnya. Sekiranya hanya ada satu ruang dalam nama, ia menghasilkan 1. Sekiranya terdapat dua ruang, hasilnya adalah 2, dan seterusnya.
Dalam nama contoh di B4, terdapat dua ruang dalam namanya, jadi kami mendapat:
15 - 13 = 2
Dan dua digunakan seperti dalam nombor contoh:
SUBSTITUTE(B4," ","*",2)
yang menggantikan ruang kedua dengan "*". Nama itu kelihatan seperti ini:
"Susan Ann * Chang"
Fungsi FIND kemudian mengambil alih untuk mengetahui di mana "*" ada dalam nama:
FIND("*", "Susan Ann*Chang")
Hasilnya adalah 10 (* berada di kedudukan ke-10) yang dikurangkan dari jumlah panjang nama:
LEN(B4)-10
Oleh kerana namanya adalah 15 aksara, kami mempunyai:
15-10 = 5
Nombor 5 digunakan oleh KANAN seperti:
=RIGHT(B4,5)
Yang menghasilkan "Chang"
Seperti yang anda lihat, banyak kerja di atas untuk mengira 5 yang mudah!
Mengendalikan ruang yang tidak konsisten
Ruang tambahan akan menimbulkan masalah dengan formula ini. Salah satu penyelesaiannya ialah menggunakan fungsi TRIM terlebih dahulu untuk membersihkan sesuatu, kemudian menggunakan formula penghuraian.
