Intinya. Pelajari pro dan kontra dari fungsi pemisahan teks baru di Excel, dan mana yang menjadi favorit saya
Tingkat keahlian. Intermediat
Tonton Tutorialnya
Tonton di YouTube & Berlangganan ke Saluran kamiUnduh File Excel
Lembar latihan yang saya gunakan dalam video dapat diunduh di sini
TEXTSPLIT-vs-TEXTBEFORE-AFTER. xlsxDownload
Fungsi Baru untuk Memisahkan Teks
Microsoft baru-baru ini merilis beberapa fungsi baru yang hebat untuk memisahkan teks dari satu kolom menjadi beberapa kolom
Fungsi baru ini saat ini hanya tersedia untuk Saluran Beta Insider untuk Microsoft 365, tetapi diharapkan akan diluncurkan ke saluran lain dalam beberapa bulan mendatang. Sampai saat itu, jika Anda sedang mencari cara untuk memisahkan teks, lihat tutorial saya untuk memisahkan teks menggunakan Power Query, Teks ke Kolom, rumus, atau Isian Cepat
Yang pertama dari fungsi ini adalah TEXTSPLIT, yang mencari contoh apa pun dari pembatas yang ditentukan dalam sel dan kemudian menumpahkan rentang untuk setiap grup teks di antara pembatas tersebut. Apa itu pembatas? . Pembatas yang paling umum adalah koma dan spasi
Dua fungsi lainnya sering digunakan bersama. Yang pertama adalah TEXTBEFORE, yang mengembalikan teks apa pun yang muncul sebelum pembatas yang Anda tentukan. Yang kedua adalah TEXTAFTER, yang (seperti yang bisa Anda tebak) mengembalikan teks yang terjadi setelah pembatas
TEXTSPLIT menawarkan beberapa opsi dan keuntungan yang luar biasa, tetapi saya sebenarnya lebih menyukai TEXTBEFORE dan TEXTAFTER dan lebih sering menggunakannya. Anda akan mengetahui alasannya saat kami melihat cara menggunakan fungsi baru yang luar biasa ini
TEKSPLIT
Fungsi TEXTSPLIT sangat mudah digunakan. Hanya ada dua argumen yang perlu Anda identifikasi. Yang pertama adalah teks yang ingin Anda bagi. Yang kedua adalah pembatas kolom (col_delimiter). Dalam contoh sederhana kami, saya telah menggunakan sel di kolom A sebagai teks, dan spasi (“”) sebagai pembatas
Karena ini adalah fungsi larik dinamis, ini akan memisahkan teks di mana pun ia mendeteksi spasi dan menumpahkan setiap grup teks tersebut ke dalam kolomnya sendiri. Dalam contoh kita, hanya ada dua grup teks (nama depan dan nama belakang), sehingga mengembalikan dua kolom. Anda dapat menyeret gagang isian ke bawah dari sel pertama ke sel di bawahnya untuk menyalin rumus/hasil
Fungsi yang cepat dan mudah ini luar biasa, tetapi salah satu batasan terbesarnya adalah tidak dapat digunakan dengan Tabel Excel. Itu karena Tabel Excel tidak dapat menangani rentang tumpahan (setidaknya, belum). Jadi saat bekerja dengan Tabel Excel, opsi yang lebih baik adalah menggunakan TEXTBEFORE dan TEXTAFTER
TEXTBEFORE dan TEXTAFTER
Sama seperti dengan TEXTSPLIT, saat menulis formula kita, kita hanya perlu mengidentifikasi teks yang ingin kita tarik dan pembatas yang ingin kita pisahkan. TEXTBEFORE akan mengembalikan grup teks apa pun yang ditemukan sebelum pembatas dan TEXTAFTER akan mengembalikan teks setelahnya
Jadi untuk kolom Nama Depan kita, kita akan menggunakan TEXTBEFORE karena nama depan muncul sebelum spasi (pembatas kita). Untuk kolom Nama Belakang, kami akan menggunakan TEXTAFTER
Dan seperti yang dapat Anda bayangkan, proses yang sama digunakan untuk TEXTAFTER di kolom Nama Belakang
Banyak Pembatas
Semua itu mudah dipahami dan dijalankan karena ini adalah contoh yang sangat mendasar. Tapi bagaimana bila kita memiliki beberapa entri yang memiliki lebih dari dua nama?
Menggunakan TEXTSPLIT akan menempatkan masing-masing nama tersebut di kolom terpisah, tetapi seperti yang Anda lihat, karena ada variasi dalam jumlah nama (beberapa baris memiliki 2, lainnya 3 atau 4), nama tidak selalu sejajar dengan yang tepat.
Jelas, itu bukan solusi terbaik. Mari kita lihat bagaimana TEXTBEFORE dan AFTER dapat membantu
Mulailah dengan Nama Depan
Untuk membuat kolom Nama Depan, kami menggunakan proses yang sama yang kami gunakan sebelumnya. Kami menarik nama depan dengan menggunakan TEXTBEFORE
Kemudian urus Nama Belakang
Untuk membuat kolom Last Name caranya sedikit berbeda. Kami akan menggunakan TEXTAFTER, tetapi karena pembatas bisa lebih dari satu, kami ingin menarik teks setelah pembatas terakhir. Jadi kami akan menambahkan satu argumen lagi ke formula kami. nomor contoh. Jika kita menentukan 1 negatif sebagai nomor contoh kita, Excel akan menarik teks setelah contoh pertama bergerak mundur melalui string teks. Jadi tidak peduli berapa banyak nama yang muncul di sel, itu akan selalu menarik yang terakhir
Identifikasi Nama Tengah
Menarik nama tengah ke dalam kolom terpisah menjadi sedikit lebih rumit dalam hal rumus yang perlu kita tulis. Kami akan menggunakan TEXTBEFORE dan TEXTAFTER dalam rumus yang sama
- Rumus dimulai dengan menggunakan TEXTAFTER untuk mengeluarkan semua teks yang muncul setelah spasi pertama (sehingga menghapus nama depan)
- Kemudian menggunakan TEXTBEFORE untuk menarik apa pun yang ada sebelum spasi terakhir (menghapus nama belakang)
- Akhirnya kami membungkus semua itu dalam fungsi IFERROR, pada dasarnya memberi tahu Excel untuk mengembalikan kosong jika ada pesan kesalahan. Kenapa bisa ada pesan error?
Rumusnya terlihat seperti ini
Memisahkan Jenis Teks Lain
Fungsinya tidak hanya berguna untuk memisahkan nama. Pada contoh di bawah ini, saya ingin memisahkan nama file dari ekstensi file. Seperti sebelumnya, kita dapat menggunakan TEXTAFTER dengan negatif 1 untuk mengeluarkan teks yang muncul setelah instance terakhir dari pembatas. Dalam hal ini, pembatasnya adalah titik, bukan spasi
Formula itu jauh lebih sederhana daripada cara lama melakukannya. Rumus saya sebelum TEXTAFTER akan terlihat seperti ini
=KANAN(A5,LEN(A5)-FIND(“”,GANTI(A5,”. ”,””,LEN(A5)-LEN(GANTI(A5,”. ”,””))))))
Membagi menjadi Baris, Bukan Kolom
Salah satu hal hebat tentang TEXTSPLIT adalah Anda memiliki kemampuan untuk membagi teks menjadi baris, bukan hanya kolom. Anda melakukannya dengan menggunakan salah satu argumen opsional lainnya dalam rumus. pembatas baris. Dalam contoh saya di bawah ini, saya dapat mengubah sel dengan beberapa item yang dipisahkan koma menjadi daftar
Pemisahan menjadi Baris DAN Kolom
Sesuatu yang lebih mengesankan adalah kenyataan bahwa TEXTSPLIT dapat secara bersamaan membagi teks menjadi baris dan kolom. Pada contoh di bawah ini, titik dua digunakan untuk memisahkan teks menjadi kolom sedangkan koma digunakan untuk memisahkan teks menjadi baris
Pos terkait
Jika Anda belum memiliki fungsi baru, kami memiliki tutorial untuk memisahkan teks menggunakan Power Query, Teks ke Kolom, rumus, atau Isian Cepat
Jika Anda tertarik untuk melihat fungsi baru lainnya yang baru saja keluar, lihat postingan ini
14 Fungsi Excel Baru. Pemisahan Teks dan Pemrosesan Array
Dan tutorial ini memandu Anda melalui cara kerja rumus array dinamis dan rentang tumpahan secara lebih mendetail
Formula Array Dinamis & Rentang Tumpahan
Kesimpulan
TEXTSPLIT, TEXTBEFORE, dan TEXTAFTER adalah fungsi "sehari-hari" hebat yang dapat Anda lakukan dalam banyak tugas pembersihan data
Sejak mendapatkan fungsi baru ini beberapa minggu yang lalu, saya mendapati diri saya lebih sering menggunakan TEXTBEFORE dan TEXTAFTER. Sebagian besar karena ada kompatibilitas dengan Tabel Excel dan kemampuan untuk melakukan ekstraksi lebih lanjut, seperti yang saya tunjukkan pada contoh nama tengah di atas
Bisakah Anda melihat diri Anda menggunakan TEXTSPLIT, TEXTBEFORE, dan TEXTAFTER?
Sebelumnya Tantangan Excel. Game Tebak Baby Shower
Berikutnya Ulang Ulang Pertarungan Pakar FMWC – Square of Fortune
Anda mungkin juga suka
Referensi Seluruh Kolom dapat membuat Anda MASALAH dengan Excel
Tantangan Rumus Excel. Plat Genap atau Ganjil
Cara Memperbaiki dan Mencegah Kesalahan #VALUE dengan VLOOKUP
Kapan menggunakan VLOOKUP alih-alih XLOOKUP
Siegfried Rendl .
Hallo John, ja leider werden wir da noch länger warten müssen bis MicroSoft auch uns (Excel in German) zur Verfügung stellt
Ich finde gerade Textbefor und Textafter as sehr brauchbar, aber bruder derzeit in meinem Excel MS-365 nicht verwendbar
kata Jon Acampora .
Sayang sekali Anda harus menunggu fungsi di Excel versi Jerman. Mereka pasti layak untuk ditunggu. 🙂
kata Allen Weber .
Bagus. Tapi saya secara rutin perlu membagi nama lengkap dengan segala macam anomali. Berikut beberapa contohnya
Pertama Tengah1 Tengah2 Terakhir
Pertama Terakhir Jr
Pertama Terakhir Sr. , MD
Pertama Tengah Terakhir, RN, RNP, AACP
Pertama Tengah Terakhir1-Terakhir2
Pertama Pertama Tengah Terakhir
Pertama Tengah Terakhir1 Terakhir2, DDS
Pemisahan nama adalah tugas… 🙁
kata Jon Acampora .
Hai Allen,
Salah satu fitur bagus dari fungsi teks baru adalah Anda dapat menentukan beberapa pembatas. Anda melakukan ini dengan membuat daftar pembatas terpisah koma yang dibungkus dengan kurung kurawal{” “,”, “,”-“}
Saya setuju bahwa pemisahan nama masih merupakan tugas dan beberapa skenario memerlukan pekerjaan tambahan. Tetapi opsi pembatas berganda bisa berguna
kata Bonisiwe .
Hai, ini bukan komentar tapi pertanyaan, bolehkah saya tahu rumus menghapus "0" pertama di nomor sel (e. g 084 4705 03 9)menggunakan fungsi teks
kata Jon Acampora .
Pertanyaan bagus. Ada beberapa cara untuk melakukannya. Inilah salah satu solusi yang mungkin
=JIKA(NILAI(KIRI(A1,1))=0,KANAN(A1,LEN(A1)-1),A1)
Rumus ini memeriksa apakah karakter pertama adalah 0 menggunakan fungsi LEFT. Fungsi VALUE mengonversi teks menjadi angka sebelum melakukan perbandingan
Jika ada 0 di awal, fungsi RIGHT digunakan untuk mengembalikan sisa karakter dengan mencari jumlah karakter dengan LEN dan mengurangkan 1
saya harap itu membantu. Sekali lagi terima kasih dan semoga harimu menyenangkan. 🙂
kata Peter .
Hi Jon
Ini, seperti kebanyakan video Anda, sangat membantu. Sayangnya, salinan Excel 365 saya tidak memiliki fungsi ini. Saya tidak dapat menemukan penjelasan online. Apa yang harus saya lakukan?
kata Mike .
Dalam contoh pertama Anda, jika Anda mengganti "A5" dengan "A5. A18”, fungsi hanya mengembalikan kata pertama dari setiap sel, alih-alih setiap kata dari setiap sel dibagi menjadi kolom (mis. g. , “Ardisj” bukan {“Ardisj”,”Flindall”})
Excel modern biasanya menangani input array dengan sangat baik, jadi saya tidak yakin apakah ini bug atau apakah ada trik lain yang diperlukan untuk membuatnya berfungsi