Kapan textsplit tersedia di excel

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 kami

Unduh 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

Apakah Textsplit tersedia di Excel?

Jika Anda menggunakan Excel 365, TEXTSPLIT adalah salah satu fungsi teks baru yang mempermudah memisahkan alamat lengkap, atau string teks lainnya, ke dalam kolom, menggunakan rumus.

Mengapa Textsplit tidak tersedia di Excel?

Fungsi TEXTSPLIT hanya tersedia di Excel untuk Microsoft 365 (Windows dan Mac) dan Excel untuk web . Kiat. Di versi Excel di mana fungsi TEXTSPLIT tidak tersedia (selain Excel 365), Anda bisa menggunakan panduan Teks ke Kolom untuk membagi sel.

Bagaimana cara mengaktifkan Textsplit di Excel?

Sintaks .

teks Teks yang ingin Anda pisahkan. .

col_delimiter Teks yang menandai titik untuk menumpahkan teks ke seluruh kolom

row_delimiter Teks yang menandai titik untuk menumpahkan teks ke bawah baris. .

abaikan_kosong Tentukan BENAR untuk mengabaikan pembatas berurutan. .

match_mode Tentukan 1 untuk melakukan pencocokan case-insensitive

Mengapa Vstack tidak muncul di Excel?

Baik VSTACK dan HSTACK dapat ditemukan dalam kategori Pencarian & Referensi di Pustaka Fungsi Excel. Jika tidak, maka baik. Anda tidak memiliki langganan Microsoft 365 saat ini, atau . Anda tidak berada di saluran Office Insider .