Tutorial Excel – ekstrak teks atau angka berdasarkan posisi tertentu

Dalam banyak kasus, Anda mungkin hanya perlu mengekstrak konten yang berguna dari kalimat atau string teks dalam sel, seperti mengekstrak provinsi dari alamat, mengekstrak alamat email dari kalimat, mengekstrak nomor tagihan kurir dari percakapan, dan seterusnya. Tutorial ini mempersempit ekstraksi ke posisi tertentu dalam sel dan mengumpulkan metode yang berbeda untuk membantu mengekstrak teks atau angka dari sel dengan posisi tertentu di Excel.

---

Ekstrak teks berdasarkan posisi

Bagian ini mengumpulkan posisi umum di mana teks dapat diekstraksi dari sel dan menyediakan metode yang sesuai untuk menanganinya langkah demi langkah. Anda dapat menelusuri untuk lebih jelasnya.

1. Ekstrak jumlah karakter dari kiri atau kanan

Untuk mengekstrak jumlah karakter dari sisi kiri atau kanan string, Anda dapat mencoba salah satu metode di bawah ini.

1.1 Ekstrak karakter N pertama atau terakhir dengan rumus

Misalkan Anda memiliki daftar string teks di kolom B seperti yang ditunjukkan pada gambar di bawah, untuk mengekstrak 2 karakter pertama dan 2 karakter terakhir dari setiap string, Anda dapat menerapkan rumus berikut.

Ekstrak karakter N pertama dari string teks

Fungsi LEFT dapat membantu mengekstrak N karakter pertama dengan mudah dari string teks di Excel.

Formula umum

=LEFT(text_string,[num_chars])

kasus

Sekarang Anda dapat menerapkan rumus ini untuk mengekstrak 2 karakter pertama dari sel di kolom B.

1. Pilih sel kosong, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil pertama. Pilih sel hasil dan seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ke sel lain.

=LEFT(B5,2)

Sekarang 2 karakter pertama di setiap sel rentang B5:B10 telah diekstraksi.

Ekstrak karakter N terakhir dari string teks

Di sini kita menerapkan fungsi KANAN untuk mengekstrak karakter N terakhir dari string teks di Excel.

Formula umum

=RIGHT(text_string,[num_chars])

kasus

Pilih sel kosong, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Kemudian pilih sel hasil ini dan seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan hasil lainnya.

=RIGHT(B5,2)

1.2 Ekstrak karakter N pertama atau terakhir dengan alat yang luar biasa

Meskipun rumus di atas sederhana, untuk mengekstrak n karakter pertama atau terakhir dari daftar panjang string teks, Anda masih perlu menyeret Pegangan IsiOtomatis dari atas ke bawah yang mungkin sedikit memakan waktu. Di sini merekomendasikan Kutools untuk Excel'S Ekstrak Teks utilitas untuk membantu mengekstrak karakter N pertama atau terakhir dari daftar string teks secara massal.

1. Pilih daftar string teks yang ingin Anda ekstrak teksnya terlebih dahulu dan klik Kutools > Teks > Ekstrak Teks.

2. Dalam bermunculan Ekstrak Teks kotak dialog, Anda perlu mengkonfigurasi sebagai berikut.

Note: Untuk membuat hasil dinamis ketika string teks berubah, Anda dapat memeriksa Sisipkan sebagai rumus kotak.

3. Pada popping up berikutnya Ekstrak Teks kotak dialog, pilih sel untuk menampilkan karakter yang diekstraksi dan kemudian klik OK.

Kemudian karakter N pertama atau terakhir yang ditentukan diekstraksi dari sel yang dipilih secara massal.

Klik untuk mengetahui lebih banyak tentang fitur ini.

---

2. Ekstrak teks sebelum atau sesudah karakter/kata tertentu

Untuk mengekstrak teks sebelum atau sesudah karakter atau kata tertentu, skenario yang berbeda di bagian ini akan memenuhi kebutuhan Anda.

2.1 Ekstrak teks sebelum atau setelah pembatas pertama (karakter)

Seperti yang ditunjukkan pada tangkapan layar di bawah, untuk mengekstrak teks sebelum atau setelah pembatas pertama dari setiap sel dalam rentang B4:B10, Anda dapat menerapkan salah satu metode di bawah ini.

2.1.1 Ekstrak teks sebelum pembatas pertama dengan rumus

Menerapkan rumus berdasarkan fungsi LEFT dan FIND dapat membantu mengekstrak teks sebelum pembatas pertama dari sel. Anda dapat mengikuti langkah-langkah di bawah ini untuk menyelesaikannya.

Formula umum

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

kasus

Pilih sel kosong, salin atau masukkan rumus di bawah ini ke dalamnya dan tekan tombol Enter kunci untuk mendapatkan hasil pertama. Pilih sel hasil pertama dan seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan teks sebelum pembatas pertama sel lain.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Ekstrak teks setelah pembatas pertama dengan rumus

Rumus di bawah ini membantu mengekstrak teks setelah pembatas pertama dari sel di Excel.

Formula umum

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

kasus

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 Ekstrak teks sebelum atau sesudah pembatas pertama dengan alat yang luar biasa

Di sini sangat merekomendasikan Ekstrak Teks kegunaan Kutools untuk Excel. Dengan fitur ini, Anda dapat dengan mudah mengekstrak teks sebelum atau sesudah pembatas pertama dari rentang sel secara massal.

1. Pilih rentang sel tempat Anda ingin mengekstrak teks, lalu klik Kutools > Teks > Ekstrak Teks.

2. Dalam Ekstrak Teks kotak dialog, Anda perlu mengkonfigurasi sebagai berikut.

Catatan: Untuk membuat hasil dinamis saat string teks berubah, Anda dapat mencentang kotak Sisipkan sebagai rumus.

3. Kemudian lainnya Ekstrak Teks kotak dialog muncul, pilih sel untuk menampilkan hasil dan klik OK.

Kemudian teks sebelum atau setelah pembatas pertama diekstraksi dari sel yang dipilih sekaligus.

Untuk mengetahui lebih lanjut tentang fitur ini, silakan kunjungi: Ekstrak Teks Tertentu Dengan Cepat Dari Sel Di Excel.

2.2 Ekstrak teks sebelum atau sesudah pembatas terakhir (karakter)

Pada langkah-langkah di atas kita telah mempelajari metode tentang mengekstrak teks sebelum atau sesudah pembatas pertama dari sel. Seperti yang ditunjukkan pada tangkapan layar di bawah, bagian ini akan menunjukkan kepada Anda dua rumus untuk mengekstrak teks sebelum atau sesudah pembatas terakhir dari sel. Anda dapat mengikuti langkah-langkah di bawah ini untuk menyelesaikannya.

2.2.1 Ekstrak teks sebelum pembatas terakhir dengan rumus

Untuk mengekstrak teks sebelum pembatas terakhir dari sel, Anda dapat menggunakan fungsi SEARCH, LEN dan SUBSTITUTE di dalam fungsi LEFT.

Formula umum

=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)

kasus

Pilih sel, masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini dan seret Pegangan IsiOtomatisnya ke bawah untuk mengekstrak teks dari string teks lain di kolom yang sama.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Ekstrak teks setelah pembatas terakhir dengan rumus

Setelah mengekstrak teks sebelum pembatas terakhir dari sel, Anda dapat menerapkan rumus di bawah ini untuk mengekstrak teks setelah pembatas terakhir sesuai kebutuhan.

Formula umum

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

kasus

Pilih sel, masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini dan seret Pegangan IsiOtomatisnya ke bawah untuk mengekstrak teks dari string teks lain di kolom yang sama.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 Ekstrak teks setelah karakter ke-n

Lihat contoh di bawah ini, ada daftar string teks dalam rentang B4:B10, untuk mengekstrak teks setelah karakter ketiga dari setiap sel, Anda dapat menerapkan rumus berdasarkan fungsi MID dan fungsi LEN.

Formula umum

=MID(text_string,nth_char+1,LEN(text_string))

kasus

Pilih sel kosong, salin atau masukkan rumus di bawah ini ke dalamnya dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini dan seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan hasil lainnya.

=MID(B5,3+1,LEN(B5))

2.4 Ekstrak kata ke-n dari string teks

Misalkan Anda memiliki daftar string teks seperti yang ditunjukkan pada tangkapan layar di bawah, dan ingin mengekstrak hanya kata ke-n dari string teks, bagian ini menyediakan tiga metode bagi Anda untuk menyelesaikannya.

2.4.1 Ekstrak kata ke-n dengan rumus

Anda dapat menggabungkan fungsi TRIM, MID, SUBSTITUTE, REPT dan LEN untuk mengekstrak kata ke-n dari string teks dalam sel.

Formula umum

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))

kasus

Dalam hal ini, rentang B5:B10 berisi string teks, D5:D10 berisi angka yang mewakili kata ke-n, mari terapkan rumus ini untuk mengekstrak kata ke-n dari string teks.

Pilih sel kosong, salin atau masukkan rumus di bawah ini ke dalamnya dan tekan tombol Enter kunci untuk mendapatkan hasil pertama. Pilih sel hasil ini dan seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan kata ke-n dari sel lain.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Catatan: Anda bisa langsung mengetikkan angka ke-n pada rumus sebagai berikut.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Ekstrak kata ke-n dengan fungsi yang ditentukan pengguna

Terlepas dari rumus di atas, Anda juga dapat menerapkan fungsi yang ditentukan pengguna untuk mengekstrak kata ke-n dari sel di Excel.

1. tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, dan kemudian salin VBA di bawah ini ke jendela Kode.

Kode VBA: Ekstrak kata ke-n dari string teks dalam sel

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

4. Kembali ke lembar kerja yang berisi string teks yang ingin Anda ekstrak kata ke-n. Pilih sel kosong, salin atau masukkan rumus di bawah ini ke dalamnya dan tekan tombol Enter kunci untuk mendapatkan kata ke-n.

=FindWord(B5,D5)

Or

=FindWord(B5,2)

Catatan: dalam rumus, D5 adalah sel yang berisi angka yang mewakili kata ke-n. Atau, Anda dapat langsung mengganti referensi sel dengan angka.

5. Pilih sel hasil dan seret Pegangan IsiOtomatisnya ke bawah untuk mengekstrak kata ke-n dari string teks sel lain.

2.4.3 Ekstrak kata ke-n dengan alat yang luar biasa

Jika Anda tidak ingin secara manual menerapkan rumus atau fungsi yang ditentukan pengguna yang disediakan di atas, di sini merekomendasikan Kutools for Excel's Ekstrak kata ke-n dalam sel kegunaan. Dengan fitur ini, Anda dapat dengan mudah mengekstrak kata ke-n dari string teks dalam sel hanya dengan beberapa klik.

1. Pilih sel untuk menempatkan hasilnya, dan klik Kutools > Pembantu Formula > Teks > Ekstrak kata ke-n dalam sel. Lihat tangkapan layar:

2. Dalam Rumus Pembantu kotak dialog, Anda perlu mengkonfigurasi sebagai berikut.

3. Kemudian kata ke-n (kedua) diekstraksi dari string teks di sel B5, dan Anda dapat melihat rumus dibuat pada saat yang sama. Pilih sel hasil ini dan seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan kata ke-n dari string teks lainnya.

Klik untuk mengetahui lebih banyak tentang fitur ini.

2.5 Ekstrak teks sebelum atau setelah kemunculan ke-n dari pembatas

Misalkan Anda memiliki daftar string teks seperti yang ditunjukkan pada gambar di bawah. Untuk mengekstrak teks sebelum atau setelah kemunculan kedua spasi, bagian ini menyediakan dua rumus untuk membantu Anda menyelesaikannya.

2.5.1 Ekstrak teks sebelum kemunculan ke-n dari pembatas

Anda dapat menggunakan fungsi LEFT bersama dengan fungsi SUBSTITUTE dan FIND untuk mengekstrak teks sebelum kemunculan ke-n pembatas dari sel di Excel.

Formula umum

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

kasus

Pilih sel, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini dan seret AutoFill Handle ke bawah untuk mendapatkan hasil lain dalam daftar.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Catatan: Dalam rumus, B5 adalah sel yang berisi string teks yang ingin Anda ekstrak teksnya; “ ” di sini mewakili spasi dan angka 2 mewakili kemunculan kedua spasi. Anda dapat mengubahnya sesuai dengan kebutuhan Anda.

2.5.2 Ekstrak teks setelah kemunculan pembatas ke-n

Untuk mengekstrak teks setelah kemunculan pembatas ke-n, Anda dapat menerapkan fungsi KANAN dengan fungsi SUBSTITUTE, LEN, dan FIND.

Formula umum

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

kasus

Sekarang Anda dapat menerapkan rumus ini untuk mengekstrak teks setelah kemunculan spasi kedua dari setiap sel dalam rentang B5:B10 sebagai berikut.

Pilih sel, masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini dan seret AutoFill Handle ke bawah untuk mendapatkan hasil lainnya.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 Ekstrak teks sebelum atau sesudah jeda baris

Misalkan Anda memiliki daftar pesanan di kolom B dan Anda ingin mengekstrak hanya bagian tanggal dan bagian No. produk dari setiap sel. Anda dapat menggunakan rumus Excel di bawah ini untuk menyelesaikannya.

2.6.1 Ekstrak teks sebelum jeda baris pertama dengan rumus

Seperti yang Anda lihat pada tangkapan layar di atas, bagian tanggal ditempatkan sebelum jeda baris pertama di dalam sel. Bagian ini akan mendemonstrasikan fungsi LEFT bersama dengan fungsi SEARCH untuk membantu Anda mengekstrak teks sebelum jeda baris pertama di dalam sel.

Formula umum

=LEFT(cell, SEARCH(CHAR(10), cell)-1)

kasus

Pilih sel kosong, salin atau masukkan rumus di bawah ini ke dalamnya dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ini ke sel lain.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Kemudian Anda dapat melihat teks sebelum jeda baris pertama di setiap sel dalam rentang B5:B8 diekstraksi seperti yang ditunjukkan pada gambar di bawah.

Catatan: Dalam rumus, CHAR(10) mewakili jeda baris di Windows.

2.6.2 Ekstrak teks setelah jeda baris terakhir dengan rumus

Pada langkah sebelumnya, kita berbicara tentang cara mengekstrak teks sebelum jeda baris pertama di dalam sel. Dan bagian ini akan memandu Anda cara mengekstrak teks setelah jeda baris terakhir di dalam sel dengan rumus yang berbeda.

Formula umum

=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))

kasus

Pilih sel kosong, masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ke sel lain.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Kemudian produk no. bagian dari setiap sel dalam daftar diekstraksi seperti yang ditunjukkan pada gambar di atas.

Catatan: Dalam rumus, CHAR(10) mewakili jeda baris di Windows.

2.7 Ekstrak teks sebelum atau sesudah kata

Di bagian sebelumnya, kita telah mempelajari cara mengekstrak teks sebelum atau sesudah karakter atau pembatas. Apa yang harus Anda lakukan untuk mengekstrak teks sebelum atau sesudah seluruh kata? Bagian ini akan memperkenalkan tiga metode untuk membantu Anda menyelesaikan tugas ini.

2.7.1 Ekstrak teks sebelum kata tertentu dengan rumus

Rumus berikut membantu Anda mengekstrak teks sebelum kata tertentu di dalam sel di Excel.

Formula umum

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

kasus

Pilih sel kosong, masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ini ke sel lain.

Dalam contoh ini, kita akan mengekstrak semua teks sebelum kata “Excel”, jadi kita langsung mengetik kata dalam rumus dan menyertakannya dalam tanda kutip ganda. Atau Anda dapat merujuk ke sel yang berisi kata "Excel".

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Catatan:

2.7.2 Ekstrak teks setelah kata tertentu dengan rumus

Untuk mengekstrak teks setelah kata tertentu, Anda dapat menerapkan rumus berikut untuk menyelesaikannya.

Formula umum

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

kasus

Pilih sel, masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ini ke sel lain.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Kemudian Anda dapat melihat semua teks setelah kata "Excel" di setiap sel diekstraksi seperti yang ditunjukkan pada gambar di bawah.

Catatan:

2.7.3 Ekstrak teks sebelum atau sesudah kata tertentu dengan alat yang luar biasa

Jika Anda merasa bahwa menggunakan formula dapat menyebabkan banyak ketidaknyamanan, di sini sangat merekomendasikan: Ekstrak Teks kegunaan Kutools untuk Excel. Fitur ini membantu mengotomatiskan tugas Ekstraksi di Excel hanya dengan beberapa klik.

1. klik Kutools > Teks > Ekstrak Teks untuk mengaktifkan fitur ini.

2. Dalam Ekstrak Teks kotak dialog, lakukan pengaturan berikut.

Catatan: Jika Anda ingin membuat hasil yang dinamis, periksa Sisipkan sebagai rumus kotak. Kemudian hasilnya akan otomatis terupdate ketika data dalam range berubah.

3. Kemudian Ekstrak Teks kotak dialog muncul, Anda perlu memilih sel untuk menampilkan hasilnya dan kemudian klik tombol OK .

Kemudian teks sebelum atau sesudah kata tertentu dalam setiap sel dalam rentang yang dipilih segera diekstraksi.

Catatan: Fitur ini peka huruf besar/kecil.

Klik untuk mengetahui lebih banyak tentang fitur ini.

---

3. Ekstrak antar karakter/kata

Jika Anda ingin mengekstrak teks di antara karakter atau kata tertentu, coba metode berikut.

3.1 Ekstrak teks di antara dua karakter

Untuk mengekstrak teks antara dua karakter, yang mungkin karakter yang sama atau berbeda. Bagian ini menyediakan beberapa metode, dan Anda dapat memilih salah satunya sesuai dengan kebutuhan Anda.

3.1.1 Ekstrak teks antara dua karakter yang sama dengan rumus

Seperti yang ditunjukkan pada tangkapan layar di bawah, ada daftar string teks di kolom B, dan Anda ingin mengekstrak bagian nomor di antara karakter "/" dari setiap sel dalam rentang, rumus berikut dapat membantu Anda.

Pilih sel kosong, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil lalu seret IsiOtomatis Menangani ke bawah untuk mendapatkan hasil sel lain dalam daftar.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Kemudian teks di antara dua karakter yang sama "/" diekstraksi dari setiap sel dalam rentang. Lihat tangkapan layar:

Catatan:

3.1.2 Ekstrak teks antara dua karakter yang berbeda dengan rumus

Setelah mempelajari cara mengekstrak teks antara dua karakter yang sama di dalam sel, di sini kami akan mendemonstrasikan rumus untuk mengekstrak teks antara dua karakter yang berbeda. Seperti yang ditunjukkan pada tangkapan layar di bawah, untuk mengekstrak hanya alamat email antara "<" dan ">" dari setiap sel di kolom B, Anda dapat melakukan hal berikut.

Formula umum

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

kasus

Pilih sel kosong, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ini ke sel lain.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Anda dapat melihat bahwa hanya teks di antara karakter tertentu yang diekstraksi seperti yang ditunjukkan pada tangkapan layar di atas.

3.1.3 Ekstrak teks antara dua karakter dengan alat yang luar biasa

Di sini sangat merekomendasikan Ekstrak string di antara teks tertentu fitur dari Kutools untuk Excel untuk membantu Anda dengan mudah mengekstrak teks antara dua karakter yang sama atau berbeda dalam sel di Excel.

1. Pilih sel kosong untuk menampilkan hasilnya, lalu klik Kutools > Pembantu Formula > Pembantu Formula.

2. Dalam Rumus Pembantu kotak dialog, lakukan pengaturan berikut.

3. Kemudian hanya teks antara "<" dan ">" di dalam sel B5 yang diekstraksi. Sementara itu, rumus telah dibuat, Anda dapat memilih sel hasil ini dan kemudian menyeret Pegangan IsiOtomatisnya ke bawah untuk mengekstrak teks dari sel lain dalam daftar yang sama.

Klik untuk mengetahui lebih banyak tentang fitur ini.

3.1.4 Ekstrak teks antara dua karakter (termasuk karakter) dengan aturan

Jika Anda ingin menyimpan dua karakter setelah mengekstraksi, coba terapkan aturan di fitur Ekstrak Teks Kutools for Excel.

1. klik Kutools > Teks > Ekstrak Teks.

2. Dalam Ekstrak Teks kotak dialog, lakukan pengaturan berikut.

3. Lain Ekstrak Teks kotak dialog muncul, pilih sel untuk menampilkan hasilnya, lalu klik tombol OK .

Kemudian teks di antara karakter tertentu (termasuk karakter) diekstraksi dari setiap sel dalam rentang yang dipilih secara massal.

3.2 Ekstrak teks di antara dua kata

Selain mengekstrak teks di antara dua karakter, Anda mungkin juga perlu mengekstrak teks di antara dua kata. Misalnya, ekstrak semua string teks antara dua kata "KTE" dan "fitur" dari setiap sel di kolom B seperti yang ditunjukkan pada gambar di bawah. Anda dapat mencoba salah satu metode berikut untuk menyelesaikannya.

3.2.1 Ekstrak teks antara dua kata dengan rumus

Anda bisa menggunakan rumus berdasarkan fungsi MID dan fungsi SEARCH untuk mengekstrak semua string teks di antara dua kata di dalam sel.

Formula umum

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

kasus

Pilih sel kosong, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ini ke sel lain.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Note: Dalam rumus, angka 3 mewakili panjang karakter kata "KTE"; angka 4 mewakili panjang karakter kata "KTE" ditambah 1.

Anda dapat melihat semua string teks antara dua kata yang ditentukan diekstraksi dari setiap sel di kolom B.

3.2.2 Ekstrak teks di antara dua kata dengan alat yang luar biasa

Bagi banyak pengguna Excel, rumus mungkin sulit diingat dan ditangani. Di sini, dengan Ekstrak string di antara teks tertentu fitur dari Kutools untuk Excel, Anda dapat dengan mudah mengekstrak teks di antara dua kata hanya dengan beberapa klik.

1. Pilih sel untuk menampilkan hasilnya, lalu klik Kutools > Pembantu Formula > Pembantu Formula.

2. Dalam Pembantu Formula kotak dialog, Anda perlu mengkonfigurasi sebagai berikut.

3. Kemudian semua string teks antara dua kata "KTE" dan "fitur" di dalam sel B5 diekstraksi. Sementara itu, rumus telah dibuat, Anda dapat memilih sel hasil ini dan kemudian menyeret Pegangan IsiOtomatisnya ke bawah untuk mengekstrak teks dari sel lain dalam daftar yang sama.

---

Ekstrak angka berdasarkan posisi

Untuk daftar string alfanumerik, mungkin ada tiga kasus:

1. Nomornya ada di awal teks;
2. Nomornya ada di akhir teks;
3. Nomornya bisa di mana saja di teks.

Pada bagian ini, kami akan memberikan metode berbeda yang dapat digunakan untuk mengekstrak angka dalam setiap kasus yang disebutkan di atas.

1 Ekstrak nomor dari kiri string

Bagian ini akan memperkenalkan rumus untuk membantu Anda mengekstrak hanya angka yang muncul sebelum teks dalam sel.

Formula umum

=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)

kasus

Catatan:

Pilih sel kosong, masukkan rumus di bawah ini dan tekan Ctrl + perubahan + Enter or Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan jumlah sel lain.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Catatan:

---

2 Ekstrak nomor dari kanan string

Seperti yang ditunjukkan pada tangkapan layar di bawah, untuk mengekstrak hanya angka yang muncul setelah teks dalam sel, coba rumus berikut.

Formula umum

=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))

kasus

Catatan:

Pilih sel kosong, masukkan rumus di bawah ini dan tekan Ctrl + perubahan + Enter or Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan jumlah sel lain.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

Catatan:

---

3. Ekstrak semua angka dari posisi mana pun dalam string teks

Metode di atas membantu mengekstrak nomor hanya dari kiri atau kanan string teks. Jika Anda ingin mengekstrak semua angka dari mana saja dalam string teks, di sini kami menyediakan tiga metode bagi Anda untuk menyelesaikannya.

3.1 Ekstrak semua angka dari mana saja dalam string dengan rumus

Anda dapat menerapkan rumus berikut untuk mengekstrak semua angka dari mana saja dalam string teks di Excel.

1. Pilih sel kosong, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan semua nomor dari sel B5.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. Pilih sel hasil lalu seret Pegangan IsiOtomatisnya ke bawah untuk mendapatkan semua nomor sel lainnya.

3.2 Ekstrak semua angka dari mana saja dalam string dengan VBA

Rumus di atas terlalu panjang dan rumit untuk banyak pengguna Excel. Sebenarnya, Anda dapat menjalankan skrip VBA untuk mengotomatiskan tugas di Excel. Anda dapat melakukan sebagai berikut.

1. tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul. Kemudian salin VBA di bawah ini ke jendela kode Modul.

Kode VBA: Ekstrak semua nomor dari mana saja dalam string teks

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. tekan F5 kunci untuk menjalankan kode. Dalam pembukaan KutoolsforExcel kotak dialog, pilih rentang sel tempat Anda ingin mengekstrak semua angka dari setiap sel, lalu klik tombol OK .

4. Kemudian lainnya KutoolsforExcel kotak dialog muncul. Di kotak dialog ini, pilih sel tujuan dan klik OK.

Kemudian semua nomor diekstraksi dari setiap sel dalam rentang yang dipilih secara massal.

---

4. Ekstrak nomor setelah teks tertentu

Seperti yang ditunjukkan pada tangkapan layar di bawah, untuk mengekstrak nomor apa pun setelah teks spesifik "Tidak.", bagian ini menyediakan dua metode untuk membantu Anda menyelesaikannya.

4.1 Ekstrak angka setelah teks tertentu dengan rumus

Anda dapat menerapkan rumus berikut untuk mengekstrak angka setelah teks tertentu dalam sel di Excel.

Rumus umum:

=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))

kasus

Pilih sel kosong, salin atau masukkan rumus di bawah ini dan tekan tombol Enter kunci untuk mendapatkan hasil. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ini ke sel lain.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

Catatan:

4.2 Ekstrak angka setelah teks tertentu dengan fungsi yang ditentukan pengguna

Fungsi yang ditentukan pengguna berikut ini juga dapat membantu mengekstrak angka setelah teks tertentu dalam sel. Silakan lakukan sebagai berikut.

1. tekan lain + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, lalu salin kode VBA di bawah ini ke jendela kode Modul.

Kode VBA: ekstrak angka setelah teks tertentu dalam sel

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. tekan lain + Q kunci untuk menutup Microsoft Visual Basic untuk Aplikasi jendela.

4. Pilih sel, masukkan rumus di bawah ini dan tekan tombol Enter kunci. Pilih sel hasil ini lalu seret Pegangan IsiOtomatisnya ke bawah untuk menerapkan rumus ini ke sel lain.

=GetNumberAfterTheChar(B5,"No. ")

Catatan:

---

Artikel terkait: