Sekilas, masalah sederhana dengan solusi yang tidak jelas: ekstrak kata terakhir dari sebaris teks. Nah, atau, dalam kasus umum, fragmen terakhir, dipisahkan oleh karakter pembatas yang diberikan (spasi, koma, dll.) Dengan kata lain, perlu untuk menerapkan pencarian terbalik (dari akhir ke awal) dalam string a karakter yang diberikan dan kemudian ekstrak semua karakter di sebelah kanannya.
Mari kita lihat beberapa cara tradisional untuk memilih dari: rumus, makro, dan melalui Power Query.
Metode 1. Rumus
Agar lebih mudah memahami esensi dan mekanisme rumus, mari kita mulai sedikit dari jauh. Pertama, mari kita tambah jumlah spasi antar kata dalam teks sumber kita menjadi, misalnya, 20 buah. Anda dapat melakukan ini dengan fungsi ganti. PENGGANTI (PENGGANTI) dan fungsi mengulang karakter yang diberikan N kali – ULANGI (REPT):
Sekarang kita memotong 20 karakter dari akhir teks yang dihasilkan menggunakan fungsi KANAN (BAIK):
Ini semakin hangat, kan? Tetap menghilangkan ruang ekstra menggunakan fungsi TRIM (MEMANGKAS) dan masalah akan terpecahkan:
Dalam versi bahasa Inggris, rumus kami akan terlihat seperti ini:
=TRIM(KANAN(GANTI(A1;» «;REPT(» «;20));20))
Saya harap jelas bahwa pada prinsipnya tidak perlu menyisipkan tepat 20 spasi – angka berapa pun boleh, asalkan lebih dari panjang kata terpanjang dalam teks sumber.
Dan jika teks sumber perlu dibagi bukan dengan spasi, tetapi dengan karakter pemisah lain (misalnya, dengan koma), maka rumus kami perlu sedikit diperbaiki:
Metode 2. Fungsi makro
Tugas mengekstrak kata atau fragmen terakhir dari teks juga dapat diselesaikan dengan menggunakan makro, yaitu, menulis fungsi pencarian terbalik di Visual Basic yang akan melakukan apa yang kita butuhkan – mencari substring yang diberikan dalam string dengan arah yang berlawanan – dari akhir ke awal.
Tekan pintasan keyboard lain+F11 atau tombol Visual Basic tab pembangun (Pengembang)untuk membuka editor makro. Kemudian tambahkan modul baru melalui menu Sisipkan – Modul dan salin kode berikut di sana:
Fungsi LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
Sekarang Anda dapat menyimpan buku kerja (dalam format yang mendukung makro!) dan menggunakan fungsi yang dibuat dalam sintaks berikut:
=Kata Terakhir(txt ; batasi ; n)
dimana
- txt – sel dengan teks sumber
- batasi — karakter pemisah (default — spasi)
- n – kata apa yang harus diekstraksi dari akhir (secara default – yang pertama dari akhir)
Dengan perubahan apa pun dalam teks sumber di masa mendatang, fungsi makro kami akan dihitung ulang dengan cepat, seperti fungsi lembar Excel standar.
Metode 3. Power Query
Kueri Daya adalah add-on gratis dari Microsoft untuk mengimpor data ke Excel dari hampir semua sumber dan kemudian mengubah data yang diunduh ke dalam bentuk apa pun. Kekuatan dan kesejukan add-in ini sangat hebat sehingga Microsoft telah membangun semua fiturnya ke dalam Excel 2016 secara default. Untuk Excel 2010-2013 Power Query dapat diunduh secara gratis dari sini.
Tugas kita untuk memisahkan kata atau fragmen terakhir melalui pemisah yang diberikan menggunakan Power Query diselesaikan dengan sangat mudah.
Pertama, mari kita ubah tabel data kita menjadi tabel pintar menggunakan pintasan keyboard. Ctrl+T atau perintah Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel):
Kemudian kami memuat "tabel pintar" yang dibuat ke dalam Power Query menggunakan perintah Dari tabel/rentang (Dari tabel/rentang) tab Data (jika Anda memiliki Excel 2016) atau pada tab Kueri Daya (jika Anda memiliki Excel 2010-2013):
Di jendela editor kueri yang terbuka, pada tab Transformasi (Mengubah) pilih tim Pisahkan Kolom – Dengan Pembatas (Kolom Terpisah — Dengan pembatas) dan kemudian tetap mengatur karakter pemisah dan pilih opsi Pembatas paling kananuntuk memotong tidak semua kata, tetapi hanya yang terakhir:
Setelah mengklik OK kata terakhir akan dipisahkan menjadi kolom baru. Kolom pertama yang tidak perlu dapat dihapus dengan mengklik kanan tajuknya dan memilih Remove (Menghapus). Anda juga dapat mengganti nama kolom yang tersisa di header tabel.
Hasilnya dapat diunggah kembali ke lembar menggunakan perintah Beranda — Tutup dan Muat — Tutup dan Muat ke … (Beranda — Tutup & Muat — Tutup & Muat ke…):
Dan sebagai hasilnya kita mendapatkan:
Seperti ini – murah dan ceria, tanpa formula dan makro, hampir tanpa menyentuh keyboard
Jika daftar asli berubah di masa mendatang, cukup klik kanan atau gunakan pintasan keyboard Ctrl+lain+F5 memperbarui permintaan kami.
- Memisahkan teks tempel menjadi kolom
- Parsing dan parsing teks dengan ekspresi reguler
- Mengekstrak kata pertama dari teks dengan fungsi SUBSTITUTE