Cara menggunakan TEXTBEFORE, TEXTAFTER dan TEXTSPLIT di Excel

Pada kuartal pertama tahun 2022, Microsoft mengumumkan 14 fungsi Excel baru yang akan membuat bekerja dengan operasi teks dan mengubah array menjadi lebih mudah.

Di bawah ini, kami akan membagikan contoh cara menggunakan textbefore, textafter, dan textsplit di excel.

Ketersediaan

Jika Anda ingin berlatih dan menemukan bahwa Anda tidak memiliki 14 fungsi teks dan larik baru ini, kemungkinan fungsi tersebut tidak tersedia karena:

Jangan khawatir; mereka akan segera diluncurkan ke semua pelanggan Microsoft 365, jadi sekarang adalah kesempatan Anda untuk mempelajari cara menggunakannya sehingga Anda siap.

Pisahkan string teks

TEXTBEFORE, TEXTAFTER, dan TEXTSPLIT dirancang untuk manipulasi teks dan pada akhirnya akan menjadi pilihan yang lebih disukai daripada harus menggabungkan fungsi LEFT, RIGHT, atau MID dengan SEARCH, FIND, SUBSTITUTE, atau REPLACE.

TEKS SEBELUMNYA

Tujuan

Mengembalikan semua teks yang muncul sebelum (di sebelah kiri) karakter tertentu, atau pembatas.

Sintaksis

=TEXTBEFORE(text, delimiter, [instance_num], [ignore_case])

Argumen

teks Ini adalah teks atau referensi sel yang berisi teks yang akan Anda kerjakan.
pembatas Semua teks sebelum karakter ini akan diekstraksi.
contoh_num

(opsional)

Argumen ini menentukan kemunculan pembatas mana yang harus digunakan. Standarnya contoh_num adalah 1 (artinya contoh pembatas yang diamati pertama).
abaikan_kasus

(opsional)

TRUE akan mencari contoh huruf besar dan kecil dari pembatas. FALSE akan mencari pembatas dalam kasus yang digunakan dalam pembatas argumen. Nilai defaultnya salah.

Catatan

    1. Karakter wildcard tidak diperbolehkan.
    1. Jika pembatas tidak ditemukan dalam string teks, Excel mengembalikan #VALUE! kesalahan.

Dalam contoh di bawah ini, kami akan membandingkan keunggulan TEXTBEFORE.

Fungsi TEXTBEFORE vs. LEFT

Sementara LEFT akan tetap berguna untuk memisahkan teks setelah sejumlah karakter tetap, TEXTBEFORE akan menggantikan banyak kombinasi yang sebelumnya kita gunakan untuk memisahkan teks menggunakan karakter tertentu (pembatas) untuk menandai titik pemisahan.

Pisahkan string teks dengan panjang variabel

Tentu saja, ketika jumlah karakter diketahui dan diperbaiki (misalnya, nomor telepon), menggunakan fungsi LEFT untuk membagi teks tidak menjadi masalah. String teks dengan panjang yang bervariasi, bagaimanapun, berarti bahwa kita harus menemukan cara untuk menentukan di mana satu kolom berakhir dan yang baru dimulai ( jumlah_karakter argumen). Ini sebelumnya dicapai dengan menggabungkan LEFT dengan fungsi SEARCH atau FIND untuk menemukan pembatas yang sesuai.

=LEFT(A2,SEARCH(",",A2)-1)

Tentu saja, rumus di atas bekerja dengan baik ketika prinsip-prinsip bersarang dipahami, tetapi terkadang menakutkan bagi pemula atau pengguna Excel biasa.

Dengan TEXTBEFORE kita dapat dengan mudah mengekstrak nama belakang dari kolom A dengan menggunakan koma pertama (ditempatkan dalam tanda kutip ganda) sebagai pembatas. Karena TEXTBEFORE default ke instance pertama pembatas, kita hanya memerlukan argumen yang diperlukan.

=TEXTBEFORE(A2,,)

Ekstrak substring dengan opsi peka huruf besar-kecil

Dalam contoh berikut, kita dapat mengekstrak bagian nama pengguna dari alamat email yang diakhiri dengan “@xyz.net” dengan menggabungkan KIRI dengan fungsi FIND.

=LEFT(A2,FIND(“@xyz.net”,A2)-1)

Fungsi FIND peka huruf besar/kecil, sehingga nilai huruf besar “XYZ.NET” tidak dianggap cocok dan mengembalikan #VALUE! kesalahan. Pengguna harus menggunakan fungsi SEARCH alih-alih FIND untuk melakukan pencarian case-insensitive.

=LEFT(A2,SEARCH("@xyz.net",A2)-1)

TEXTBEFORE memberikan opsi untuk mengaktifkan dan menonaktifkan sensitivitas huruf besar. Ini tidak hanya membebaskan Anda dari keharusan mengingat apakah SEARCH atau FIND peka huruf besar/kecil, tetapi juga dari keharusan menggabungkan fungsi sejak awal.

Untuk membuat pembatas huruf menjadi sensitif, kita dapat menghilangkan abaikan argumen_case atau pilih SALAH.

=TEXTBEFORE(A2,"@xyz.net",,FALSE)

Perhatikan bahwa contoh_num argumen dilewati hanya dengan mengetik koma untuk melanjutkan ke argumen berikutnya.

=TEXTBEFORE(A2,"@xyz.net")

Hasilnya sama.

Untuk mengembalikan semua nama pengguna “@xyz.net”, apa pun kasusnya, kami akan memasukkan TRUE untuk abaikan_kasus argumen.

=TEXTBEFORE(A2,”@xyz.net”,,TRUE)

Catatan – Untuk mencegah Excel menampilkan kesalahan, pertimbangkan untuk menggunakan fungsi IFERROR.

SETELAH TEKS

Tujuan

Mengembalikan semua teks yang muncul setelah (di sebelah kanan) karakter tertentu, atau pembatas.

Sintaksis

=TEXTAFTER(text, delimiter, [instance_num], [ignore_case])

Argumen

teks Ini adalah teks atau referensi sel yang berisi teks yang akan Anda kerjakan.
pembatas Semua teks setelah karakter ini akan diekstraksi.
contoh_num

(opsional)

Argumen ini menentukan kemunculan pembatas mana yang harus digunakan. Standarnya contoh_num adalah 1.
abaikan_kasus

(opsional)

TRUE akan mencari pembatas huruf besar atau kecil. FALSE akan mencari pembatas dalam kasus yang digunakan. Nilai defaultnya salah.

Catatan

    1. Karakter wildcard tidak diperbolehkan.
    1. Jika pembatas tidak ditemukan dalam string teks, Excel mengembalikan #VALUE! kesalahan.

Fungsi TEXTAFTER vs. KANAN

Karena TEXTAFTER mengekstrak teks setelah pembatas yang diberikan, ini dapat dibandingkan dengan fungsi RIGHT, yang membagi teks ke akhir string teks.

Fungsi KANAN bekerja dengan baik ketika kita tahu persis berapa banyak karakter yang ingin kita ekstrak. Misalnya, kita akan mendapatkan empat digit terakhir dari nomor telepon dengan entri:

=RIGHT(A2,4)

Tetapi ketika panjang string teks tidak diketahui, kami harus menemukan cara kreatif untuk menentukan di mana substring berakhir.

=RIGHT(A2,LEN(A2)-SEARCH(", ",A2)-1)

Rumus di atas mencari kemunculan pertama pembatas dan mengurangi nomor posisi itu dari panjang seluruh string. Itu kemudian disematkan (bersarang) di dalam fungsi KANAN untuk mengekstrak teks yang muncul setelah pembatas.

Dengan TEXTAFTER, rumusnya adalah:

=TEXTAFTER(A2,", ")

Itu contoh_num dan abaikan_kasus argumen di TEXTAFTER bekerja dengan cara yang sama seperti yang dijelaskan di TEXTBEFORE.

TEKSPLIT

Tujuan

Memisahkan teks menjadi kolom atau baris berdasarkan pembatas yang ditentukan.

Sintaksis

=TEXTAFTER(text, col_delimiter, [row_delimiter], [ignore_empty], [pad_with])

Argumen

teks Ini adalah teks atau referensi sel yang berisi teks yang akan Anda kerjakan.
col_delimiter Karakter ini akan diperlakukan sebagai pemisah kolom.
baris_pembatas

(opsional)

Karakter ini akan diperlakukan sebagai pemisah baris.
abaikan_kosong

(opsional)

TRUE akan menampilkan sel kosong saat Excel menemukan pembatas berurutan dalam string asli. Standarnya adalah FALSE (tidak akan menampilkan sel kosong).
pad_with

(opsional)

Teks ini akan digunakan untuk mengisi nilai yang hilang dalam larik 2D. Standarnya adalah #T/A.

Catatan

    1. col_delimiter adalah opsional ketika baris_pembatas hadir dan sebaliknya.
    1. TEXTSPLIT menghasilkan array yang tumpah. Oleh karena itu, semua sel tempat hasil akan dikembalikan harus kosong. Jika tidak, Excel akan mengembalikan #SPILL! kesalahan.

TEXTSPLIT vs. Teks ke Kolom

Perintah Text to Columns adalah alat bawaan untuk mengubah satu kolom teks menjadi beberapa kolom.

Fungsi TEXTSPLIT merupakan peningkatan dari Text to Columns karena bersifat dinamis, yaitu hasil secara otomatis diperbarui ketika data sumber berubah. Perilaku ini adalah fitur bawaan lain dari array dinamis Microsoft 365.

TEXTSPLIT vs. KIRI, KANAN, TENGAH

Keuntungan paling jelas dari fungsi TEXTSPLIT adalah efisiensinya, seperti yang ditunjukkan pada contoh berikut.

Pisahkan teks secara instan menjadi kolom terpisah

Satu fungsi sekarang dapat membagi nilai dalam sel menjadi kolom yang berbeda – tugas yang sebelumnya membutuhkan setidaknya tiga fungsi dan tiga entri berbeda. Karena hasil TEXTSPLIT secara otomatis tumpah sebagai fitur larik dinamis, tidak perlu membagi masing-masing satu per satu.

=TEXTSPLIT(A2,", ")

TEXTSPLIT vs. Power Query untuk memisahkan teks menjadi beberapa baris

Dengan bantuan Power Query, kita tidak hanya dapat membagi teks menjadi kolom tetapi juga baris. Meskipun ini relatif mudah, banyak orang masih terintimidasi oleh Power Query.

Alternatif TEXTSPLIT memungkinkan string teks dipecah menjadi kolom, baris, atau keduanya menggunakan fungsi lembar kerja.

Pada contoh di bawah ini, kita akan menempatkan setiap kalimat dalam baris baru menggunakan TEXTSPLIT.

=TEXTSPLIT(A1,,". ")

Perhatikan bahwa col_delimiter argumen dilewati karena a baris_pembatas disediakan.

Jika kita ingin memperkenalkan kembali titik di akhir setiap kalimat tanpa menduplikasi titik akhir, kita akan menggunakan fungsi SUBSTITUTE untuk menghapus titik akhir dan cukup gunakan salah satu metode penggabungan untuk menambahkannya kembali ke setiap baris.

=SUBSTITUTE(TEXTSPLIT(A1,,". "),".","")&"."

Beberapa pembatas untuk membagi teks

Untuk situasi di mana mungkin ada beberapa pembatas, konstanta array dalam format {“a”, “b”} dapat digunakan untuk argumen pembatas TEXTSPLIT masing-masing.

Dalam contoh berikut, nilai dalam sel A1 dipecah menjadi baris yang berbeda setiap kali Excel menemukan “spasi-periode” atau “spasi koma”.

=TEXTSPLIT(A1,,{". ",", "})

Menekan baris atau kolom kosong

Terkadang TEXTSPLIT mengembalikan baris atau kolom kosong karena dua pembatas muncul bersamaan, atau pembatas muncul di akhir string teks. Dibawah baris_pembatas adalah titik, jadi baris kosong dibuat di bawah sel A14.

=TEXTSPLIT(A1,,".")

Itu abaikan_kosong argumen dapat diatur ke TRUE seperti yang ditunjukkan di bawah ini untuk menekan baris kosong.

=TEXTSPLIT(A1,,".",TRUE)

Array dua dimensi

Contoh terakhir kami menunjukkan bagaimana pad_with argumen bekerja. Ketika TEXTSPLIT mengembalikan array dua dimensi, beberapa baris atau kolom kemungkinan akan memiliki nilai yang lebih sedikit daripada yang lain.

Pad_dengan adalah argumen opsional yang memberi tahu Excel cara menangani sel kosong di akhir baris yang lebih pendek. jika pad_with argumen dihilangkan, sel-sel itu akan menampilkan kesalahan #NA.

=TEXTSPLIT(A1," ",". ")

Seperti yang ditunjukkan di atas, baris 8 mengembalikan nilai hingga ke kolom P. Baris lainnya mengembalikan kesalahan #N/A dalam sel rentang tumpahan yang tidak memiliki nilai. Untuk menampilkan hasil alternatif, masukkan a pad_with nilai sebagai teks dalam tanda kutip ganda, atau dengan menggunakan referensi sel.

Misalnya, untuk menampilkan sel kosong, rumusnya adalah:

=TEXTSPLIT(A1," ",". ",,"")

Ringkasan

Karena memisahkan nilai menjadi kolom dan baris terpisah biasanya hanya langkah pertama sebelum melakukan analisis data lebih dalam, ini adalah angin segar bahwa tugas-tugas ini sekarang dapat dilakukan secara asli dengan mudah.

Baca Juga:  Cara Mengirim Pesan Teks SMS Dari Excel

Tinggalkan komentar