Loncat ke daftar isi utama

Jelajahi yang baru, bebas versi dari Kutools untuk Outlook hari ini!

 
 

Tutorial Excel – ekstrak teks atau angka berdasarkan posisi tertentu

Penulis: Siluvia Terakhir Dimodifikasi: 2022-08-18

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.

Daftar Isi: [ Bersembunyi ]

(Klik judul mana pun dalam daftar isi di bawah atau di sebelah kanan untuk menavigasi ke bab terkait.)

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

Teks_string: String teks yang berisi karakter yang ingin Anda ekstrak. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
Jumlah_karakter: Menentukan jumlah karakter yang ingin Anda ekstrak.
Num_chars harus lebih besar dari atau sama dengan nol;
Jika Num_chars yang ditentukan lebih besar dari panjang string teks, ia mengembalikan seluruh teks;
Jika Num_chars dihilangkan, diasumsikan 1.

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

Teks_string: String teks yang berisi karakter yang ingin Anda ekstrak. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
Jumlah_karakter: Menentukan jumlah karakter yang ingin Anda ekstrak.
Jumlah_karakter harus lebih besar dari atau sama dengan nol;
Jika ditentukan Jumlah_karakter lebih besar dari panjang string teks, ia mengembalikan seluruh teks;
Jika Jumlah_karakter dihilangkan, diasumsikan 1.

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.

2.1) Pastikan Anda tinggal di Ekstrak berdasarkan lokasi Tab;
2.2) Di Jarak kotak, rentang yang dipilih ditampilkan di dalam, Anda dapat mengubah ke rentang lain sesuai dengan kebutuhan Anda;
2.3) Di Opsi bagian:
Jika Anda ingin mengekstrak N karakter pertama, pilih Karakter N pertama tombol radio, lalu tentukan jumlah karakter yang akan Anda ekstrak di kotak teks. Dalam hal ini, saya memasukkan angka 2;
Jika Anda ingin mengekstrak karakter N terakhir, pilih Karakter N terakhir tombol radio, lalu tentukan jumlah karakter yang akan Anda ekstrak di kotak teks. Dalam hal ini, saya ingin mengekstrak 2 karakter terakhir dari string teks, jadi saya memasukkan angka 2 di kotak teks.
2.4) Klik OK. Lihat tangkapan layar:

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.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

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

Teks_string: String teks tempat Anda ingin mengekstrak substring. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
Pembatas: Pembatas pertama yang menentukan teks mana yang akan diekstraksi dari sel.

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

Teks_string: String teks dari mana Anda ingin mengekstrak substring. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
Pembatas: Pembatas pertama yang menentukan teks mana yang akan diekstraksi dari sel.
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 hasil lainnya.

=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.

2.1) Tetap di Ekstrak berdasarkan lokasi Tab;
2.2) Di Jarak kotak, rentang yang dipilih ditampilkan di dalam, Anda dapat mengubahnya sesuai kebutuhan;
2.3) Di Opsi bagian:
Untuk mengekstrak teks sebelum pembatas pertama, pilih sebelum teks tombol radio, lalu ketik pembatas pertama ke dalam kotak teks;
Untuk mengekstrak teks setelah pembatas pertama, pilih setelah teks tombol radio, lalu masukkan pembatas pertama ke dalam kotak teks.
2.4) Klik OK.

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.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

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

Teks_string: String teks tempat Anda ingin mengekstrak substring. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
Pembatas: Pembatas terakhir yang menentukan teks mana yang akan diekstraksi dari sel.

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

Teks_string: String teks tempat Anda ingin mengekstrak substring. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
Pembatas: Pembatas terakhir yang menentukan teks mana yang akan diekstraksi dari sel.

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

Teks_string: String teks tempat Anda ingin mengekstrak substring. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
nth_char: Angka mewakili karakter ke-n, dan Anda akan mengekstrak teks setelahnya.

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

Teks_string: String teks dari mana Anda ingin mengekstrak kata ke-n. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
N: Angka mewakili kata ke-n yang akan Anda ekstrak dari string teks.

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.

2.1) Di Pilih daftar rumus kotak, itu Ekstrak kata ke-n dalam sel opsi disorot;
2.2) Di Sel kotak, pilih sel yang berisi string teks yang ingin Anda ekstrak dari kata ke-n;
2.3) Di Nth kotak, pilih sel yang berisi nomor ke-n atau langsung masukkan nomor sesuai kebutuhan Anda;
2.4) Klik OK.

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.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

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

Teks_string: String teks dari mana Anda ingin mengekstrak teks. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
N: Angka mewakili kemunculan ke-n dari pembatas yang akan Anda ekstrak teks sebelumnya.

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

Teks_string: String teks dari mana Anda ingin mengekstrak teks. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;
N: Angka mewakili kemunculan ke-n dari pembatas yang akan Anda ekstrak teks setelahnya.

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

Sel: Sel tempat Anda ingin mengekstrak teks sebelum jeda baris pertama.

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

Sel: Sel tempat Anda ingin mengekstrak teks sebelum jeda baris pertama.

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

Sel: Sel tempat Anda ingin mengekstrak teks sebelum kata tertentu.
kata: Kata yang ingin Anda ekstrak semua teks sebelumnya. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;

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:

1) Rumus ini peka huruf besar/kecil.
2) Jika kata yang Anda masukkan memiliki duplikat di sel yang sama, rumus hanya mengekstrak teks setelah kemunculan pertama.

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

Sel: Sel tempat Anda ingin mengekstrak teks setelah kata tertentu.
kata: Kata yang ingin Anda ekstrak semua teks setelahnya. Ini bisa berupa referensi sel atau string teks aktual yang diapit tanda kutip ganda;

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:

1) Rumus ini tidak peka huruf besar/kecil.
2) Jika kata yang Anda masukkan memiliki duplikat di sel yang sama, rumus hanya mengekstrak teks setelah kemunculan pertama.

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.

2.1) Pastikan Anda berada di Ekstrak berdasarkan lokasi Tab;
2.2) Di Jarak kotak, klik tombol untuk memilih rentang sel tempat Anda ingin mengekstrak teks;
2.3) Di Opsi bagian:
Untuk mengekstrak semua teks sebelum kata, pilih sebelum teks tombol radio dan kemudian masukkan kata ke dalam kotak teks;
Untuk mengekstrak semua teks setelah kata, pilih setelah teks tombol radio dan kemudian masukkan kata ke dalam kotak teks.
2.4) Klik OK tombol. Lihat tangkapan layar:

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.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

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:

1) B5 adalah sel tempat Anda ingin mengekstrak teks antara dua karakter yang sama;
2) "/” adalah dua karakter yang sama yang ingin Anda ekstrak teks di antaranya.
Anda perlu mengubah variabel ini berdasarkan data Anda sendiri.

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

Sel: Sel tempat Anda ingin mengekstrak teks di antara dua karakter berbeda;
akhir_char: Karakter akhir dari dua karakter yang berbeda;
Mulai_char: Karakter awal dari dua karakter yang berbeda.

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.

2.1) Periksa Filter kotak dan kemudian masukkan kata "Ekstrak" ke dalam kotak teks;
2.2) Di Pilih daftar rumus kotak, klik Ekstrak string di antara teks tertentu pilihan;
2.3) Di Masukan argumen bagian:
Dalam majalah Sel kotak, pilih sel tempat Anda ingin mengekstrak teks (di sini saya memilih sel B5);
Dalam majalah Mulai karakter kotak, masukkan karakter awal dari dua karakter yang berbeda;
Dalam majalah Akhir char(s) box, masukkan karakter akhir dari dua karakter yang berbeda.
2.4) Klik Ok. Lihat tangkapan layar:

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.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

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.

2.1) Klik Ekstrak berdasarkan aturan Tab;
2.2) Di Jarak bagian, klik tombol untuk memilih rentang sel tempat Anda ingin mengekstrak teks di antara karakter;
2.3) Di Teks kotak, masukkan <*>;
Tip: "<"Dan">” adalah karakter yang ingin Anda ekstrak teksnya, * adalah karakter pengganti yang mewakili sejumlah karakter. Anda dapat memasukkan kondisi sesuai dengan kebutuhan Anda.
2.4) Klik Add tombol untuk menambahkan kondisi ke Deskripsi aturan kotak daftar;
2.5) Klik Ok tombol. Lihat tangkapan layar:

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.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

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

Sel: sel tempat Anda ingin mengekstrak semua string teks di antara dua kata;
Mulai_kata: kata awal dari dua kata yang ingin Anda ekstrak semua string teks setelahnya;
Panjang1: panjang karakter kata awal.
akhir_kata: kata akhir dari dua kata yang ingin Anda ekstrak semua string teks sebelumnya.
Panjang2: panjang karakter kata awal ditambah 1.

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.

2.1) Periksa Filter kotak dan kemudian masukkan kata "Ekstrak" ke dalam kotak teks;
2.2) Di Pilih daftar rumus kotak, klik Ekstrak string di antara teks tertentu pilihan;
2.3) Di Masukan argumen bagian:
Dalam majalah Sel kotak, pilih sel tempat Anda ingin mengekstrak teks (di sini saya memilih sel B5);
Dalam majalah Mulai karakter kotak, masukkan kata awal dari dua kata yang ingin Anda ekstrak semua string teks setelahnya;
Dalam majalah Akhiri karakter kotak, masukkan kata akhir dari dua kata yang ingin Anda ekstrak semua string teks sebelumnya.
2.4) Klik Ok. Lihat tangkapan layar:

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.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

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

Sel: Sel tempat Anda ingin mengekstrak nomor di sebelah kiri string teks.

Catatan:

1) Jika Anda menggunakan Excel 2019 dan sebelumnya, Anda perlu menekan tombol Ctrl + perubahan + Enter kunci untuk mengkonfirmasi rumus array ini.
2) Jika Anda menggunakan Excel 365 atau Excel 2021, cukup konfirmasikan rumus ini dengan Enter kunci.

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:

1) Jika sel hanya berisi angka, seluruh nomor akan diekstraksi.
2) Rumus ini hanya mengekstrak angka di sebelah kiri string teks. Jika angka ada di tengah atau di akhir string teks, angka tersebut akan diabaikan.
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

Sel: Sel tempat Anda ingin mengekstrak nomor dari awal string teks.

Catatan:

1) Jika Anda menggunakan Excel 2019 dan sebelumnya, Anda perlu menekan tombol Ctrl + perubahan + Enter kunci untuk mengkonfirmasi rumus array ini.
2) Jika Anda menggunakan Excel 365 atau Excel 2021, cukup konfirmasikan rumus ini dengan tombol Enter.

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:

1) Jika sel hanya berisi angka, seluruh nomor akan diekstraksi.
2) Rumus ini hanya mengekstrak angka dari kanan string teks. Jika angka ada di tengah atau awal string teks, angka tersebut akan diabaikan.
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

Sel: Sel tempat Anda ingin mengekstrak angka setelah teks tertentu;
teks: Teks yang ingin Anda ekstrak angka setelahnya.

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:

1) Jika sel tidak berisi teks tertentu, rumus akan mengembalikan #N/A.
2) Rumusnya peka huruf besar-kecil.

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:

1) Dalam rumus ini, B5 adalah sel tempat Anda ingin mengekstrak nomornya, dan “Tidak. ” adalah teks spesifik yang ingin Anda ekstrak nomornya setelahnya. Anda dapat mengubahnya sesuai kebutuhan.
2) Jika ada pembatas antara teks tertentu dan nomor, harap tambahkan pembatas di akhir teks. Dalam hal ini, saya menambahkan spasi setelah teks “No.”, yang akhirnya ditampilkan sebagai “No. ”.
3) Metode ini tidak peka huruf besar/kecil;
4) Jika sel tidak berisi teks tertentu, rumus akan mengembalikan hasil kosong.

Artikel terkait:

Tutorial Excel: Memisahkan Teks, Angka, dan Sel Tanggal (Pisahkan Menjadi Beberapa Kolom)
Tutorial ini dibagi menjadi tiga bagian: sel teks split, sel nomor split dan sel tanggal split. Setiap bagian memberikan contoh berbeda untuk membantu Anda mengetahui cara menangani pekerjaan pemisahan saat menghadapi masalah yang sama.
Klik untuk mengetahui lebih lanjut ...

Excel Tambahkan Teks Dan Nomor Ke Posisi Sel Yang Ditentukan
Di Excel, menambahkan teks atau angka ke sel adalah pekerjaan yang sangat umum. Seperti menambahkan spasi di antara nama, menambahkan awalan atau akhiran ke sel, menambahkan tanda hubung ke nomor sosial. Di sini, di tutorial ini, ini mencantumkan hampir semua skenario penambahan di Excel dan menyediakan metode yang sesuai untuk Anda.
Klik untuk mengetahui lebih lanjut ...

Excel Menghapus Karakter, Kata, Angka Dari String Teks
Misalkan, Anda memiliki daftar panjang string teks yang berisi karakter, angka, atau simbol spesifik lainnya. Dalam kasus tertentu, Anda mungkin perlu menghapus beberapa karakter berdasarkan posisi, seperti dari kanan, kiri atau tengah dari string teks, atau menghapus beberapa karakter yang tidak diinginkan, angka dari daftar string. Menemukan solusi satu per satu akan membuat Anda pusing, tutorial ini mengumpulkan semua jenis metode untuk menghapus karakter, kata, atau angka di Excel.
Klik untuk mengetahui lebih lanjut ...

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!