Bagaimana cara mengekstrak data dari google sheets?

Pada artikel ini, saya akan menunjukkan kepada Anda setiap cara berbeda untuk mengekstraksi angka, teks, dan tanda baca dari string di Google Sheets. Untuk melakukan ini, kami terutama akan menggunakan fungsi REGEXREPLACE, yang dapat Anda gunakan untuk mengganti / mengekstrak berbagai jenis karakter dari data Anda

Proses ini terkadang membingungkan untuk menemukan formula yang tepat yang melakukan apa yang Anda butuhkan, dan inilah mengapa saya telah memberikan begitu banyak contoh dan variasi formula yang berbeda, di luar formula yang akan saya tunjukkan di contoh utama.

Untuk mengekstrak teks dari string di Google Sheets, gunakan fungsi REGEXREPLACE, seperti ini. =REGEXREPLACE(A3,"[^[. alfa. ]]", "")

Untuk mengekstrak angka dari string di Google Sheets, gunakan fungsi REGEXREPLACE, seperti ini. =VALUE(REGEXREPLACE(A3,"[^[. angka. ]]", ""))

Rumus untuk mengekstraksi di Google Sheets

Ekstrak angka ke dalam kolom terpisah

• =SPLIT(bawah(A3),"qwertyuiopasdfghjklzxcvbnm`-=[]\;',. /[email dilindungi]#$%^&*()")

Ekstrak teks ke dalam kolom terpisah

• =SPLIT(A3,"1234567890`-=[]\;',. /[email dilindungi]#$%^&*()")

Ekstrak N karakter mulai dari Karakter N

• =TENGAH(A3,3,1)

Ekstrak karakter yang tersisa mulai dari karakter ke-N

• =MID(A3,11,LEN(A3))

Ekstrak angka dari string

• =VALUE(REGEXREPLACE(A3,"[^[. angka. ]]", ""))

Ekstrak teks dari string

• =REGEXREPLACE(A3,"[^[. alfa. ]]", "")

Hapus tanda baca

• =REGEXREPLACE(A3,"[[. tusukan. ]]", "")

Ekstrak karakter sebelum akhiran

• =REGEXEXTRACT(A3,"([[. mencetak. ]]+)Kode")
• =LEFT(A3, SEARCH("Kode",A3)-1)

Ekstrak kata / nama depan

• =REGEXEXTRACT(A3,"[^[. ruang angkasa. ]]+")
• =KIRI(A3,CARI(" ",A3)-1)

Ekstrak karakter pertama

• =REGEXEXTRACT(A3,"[^[. ruang angkasa. ]]")

Ekstrak nama belakang

• =KANAN(A3,LEN(A3)-FIND("*",GANTI(A3," ","*",LEN(A3)-LEN(GANTI(A3," ","")))))

Ekstrak kata ke-N

• =INDEX(SPLIT(A3, ""),1)

Ekstrak N karakter dari kiri / kanan

• =KIRI(A3,2)

Ada BANYAK LAGI rumus yang akan Anda pelajari untuk digunakan di seluruh artikel ini, yang dapat Anda temukan tercantum secara lengkap di lembar contekan ekstraksi Anda

Tonton video di bawah ini untuk menelusuri contoh setiap jenis rumus

Dalam artikel ini saya telah menggunakan data sumber yang sama persis di setiap contoh, sehingga Anda dapat melihat bagaimana setiap rumus ekstraksi ini bereaksi dalam berbagai situasi, dan juga agar Anda dapat dengan mudah membandingkan perbedaan halus antara rumus serupa tanpa sumber . Karena data sumber yang sama digunakan setiap saat, data ini berisi berbagai macam kombinasi karakter di setiap baris/entri, untuk memastikan bahwa banyak rumus yang digunakan dalam artikel ini dapat dipahami/diterapkan dengan kumpulan data yang sama.

Beberapa string hanya berisi teks… beberapa hanya berisi angka… banyak di antaranya berisi berbagai tanda baca, dan beberapa berisi spasi

Berikut adalah string data mentah yang akan kami ekstrak dengan berbagai cara selama pelajaran ini, jika Anda ingin menyalin/menempelkan data sumber ini ke lembar Anda sendiri sehingga Anda dapat mengikuti contoh dan menerapkan setiap rumus pada Anda

Untuk SETIAP rumus yang dibahas pada contoh di artikel ini, rumus awalnya dimasukkan ke dalam sel C3, lalu disalin/diisi hingga C12, sehingga rumus diterapkan ke rentang C3. C12. Sekali lagi, ini agar Anda dapat melihat formula ini bereaksi dalam berbagai situasi

(Jika Anda menerapkan rumus ini ke seluruh rentang atau kolom, Anda juga dapat menggunakan fungsi ARRAYFORMULA untuk menerapkan rumus ini ke seluruh rentang. )

Inilah sebabnya mengapa dalam contoh Anda akan melihat beberapa sel yang menampilkan kesalahan saat beberapa rumus diterapkan ke string/sel tertentu

Karena data sumber yang sama digunakan di setiap contoh, akan ada beberapa rumus yang tidak dapat mengekstrak data yang diminta karena tidak ada di sel tertentu.

JANGAN KHAWATIR tentang kesalahan ini dalam contoh, gunakan saja sebagai kesempatan lain untuk mempelajari bagaimana rumus bereaksi, dan gunakan situasi untuk lebih memahami jenis data/string apa yang dimaksudkan untuk ditangani oleh rumus tertentu

Jika Anda memperkirakan akan mengalami beberapa situasi kesalahan ini dengan data Anda sendiri, di mana Anda mungkin memiliki beberapa baris/entri yang tidak memiliki data yang cocok untuk diekstraksi, maka Anda dapat memilih bagaimana Anda ingin menangani kesalahan tersebut untuk kebutuhan khusus Anda.

Catatan tentang versi rumus di artikel ini

Artikel ini sangat luas, karena ada banyak cara untuk mengekstrak di Google Sheets. Jika Anda mencari rumus yang melakukan tugas tertentu, Anda mungkin ingin mencari rumus yang melakukan apa yang Anda inginkan dan menghindari yang lain, untuk menghindari kebingungan

Jika Anda ingin mempelajari masing-masing metode ini, luangkan waktu Anda… karena mungkin diperlukan beberapa sesi untuk menguasai pelajaran ini tentang mengekstraksi

Ada banyak contoh yang disertakan… dan dengan banyak contoh saya telah menyertakan beberapa variasi rumus yang melakukan tugas serupa dengan perbedaan penting

Saya juga menyertakan rumus tambahan yang melakukan tugas yang sama persis, tetapi ditulis berbeda. Ini penting karena dua alasan

#1 Anda mungkin menemukan berbagai variasi rumus ini di internet, dan Anda pasti ingin mengenalnya agar tidak bingung

#2 Beberapa variasi ini mungkin lebih intuitif bagi Anda dan lebih fleksibel untuk digunakan daripada yang lain… sehingga saat Anda mulai memahami cara kerja rumus, Anda dapat mulai menyesuaikannya sendiri

Menggunakan fungsi REGEXEXTRACT dan REGEXREPLACE

Pada artikel ini kita akan menggunakan fungsi REGEXEXTRACT dan REGEXREPLACE secara ekstensif (walaupun tidak secara eksklusif), untuk mengekstrak dari string di Google Sheets

REGEXEXTRACT memungkinkan kita mengekstrak tipe karakter tertentu, di mana REGEXREPLACE memungkinkan kita mengganti tipe karakter tertentu dengan string kosong/tertentu (yang pada dasarnya merupakan cara lain untuk mengekstraksi, kecuali mundur)

Misalnya, katakanlah kita memiliki string abc123. Jika kita mengekstrak teksnya, kita akan mendapatkan huruf abc. Jika kita mengganti angka dengan string kosong, sekali lagi kita akan mendapatkan huruf abc

REGEXREPLACE akan memungkinkan kita untuk mengganti/mengekstrak SEMUA teks, angka, atau karakter khusus dari sebuah string, di mana REGEXEXTRACT akan memungkinkan kita untuk mengekstrak SUBSTRING teks, angka, dan karakter khusus

Dengan kata lain REGEXREPLACE dapat digunakan untuk mengekstrak/mengganti SETIAP instance dari tipe karakter tertentu yang ditemukan dalam sebuah string, di mana fungsi REGEXEXTRACT dapat digunakan untuk mengekstrak BAGIAN dari string sumber tempat karakter tertentu muncul secara berurutan

(Jika tidak ada tanda tambah yang digunakan dengan kelas karakter saat menggunakan REGEXEXTRACT, itu akan mengembalikan satu karakter alih-alih string dari banyak karakter… lebih lanjut tentang ini di bawah)

Bandingkan dua fungsi di bawah ini, yang akan sering kita gunakan selama artikel ini untuk mencapai berbagai jenis ekstraksi

Deskripsi fungsi REGEXREPLACE Google Sheets

Sintaksis
REGEXREPLACE(teks, ekspresi_reguler, penggantian)

Ringkasan rumus. “Mengganti bagian dari string teks dengan string teks berbeda menggunakan ekspresi reguler. ”

Deskripsi fungsi REGEXEXTRACT Google Sheets

Sintaksis
REGEXEXTRACT(teks, ekspresi_reguler)

Ringkasan rumus. “Mengekstrak substring yang cocok menurut ekspresi reguler. ”

Ekspresi Reguler dalam fungsi REGEXREPLACE / REGEXEXTRACT

Anda akan melihat bahwa apa yang membuat semua perbedaan dalam cara kerja kedua rumus ini, adalah "Ekspresi Reguler" di masing-masing rumus.

Ekspresi reguler memungkinkan kita untuk menentukan jenis karakter apa yang ingin kita tentukan dalam rumus kita (mis. e. teks, angka dll. ), dengan menggunakan apa yang disebut "Kelas Karakter"

Google Spreadsheet menawarkan beberapa cara berbeda untuk menulis ekspresi/kelas karakter yang menjalankan fungsi yang sama, dan inilah mengapa Anda akan melihat rumus yang terlihat berbeda tetapi melakukan hal yang sama

Misalnya, ekspresi [0-9] sama dengan ekspresi [[. angka. ]] sama dengan ekspresi \d (versi singkatan)

Kami akan menggunakan versi non-singkatan dari kelas ekspresi/karakter dalam artikel ini sebagai contoh, karena meskipun versi singkatan populer di internet, tidak ada versi singkatan untuk setiap kelas karakter, dan beberapa di antaranya

Di bawah ini saya akan membuat daftar beberapa kelas karakter dan jenis karakter apa yang diekspresikan masing-masing. Perhatikan bahwa saat menggunakan sebagian besar "kelas karakter" seperti [. angka. ], itu harus diletakkan di dalam tanda kurung kedua saat digunakan sebagai ekspresi dalam rumus, seperti [[. angka. ]]. Ini bisa membingungkan karena beberapa kelas karakter seperti [a-zA-Z] dan [0-9] tidak memerlukan tanda kurung ganda

Konten ini awalnya dibuat dan ditulis oleh SpreadsheetClass. com

Kelas Karakter untuk REGEXREPLACE dan REGEXEXTRACT

Karakter Abjad (Huruf)
[. alfa. ]  ~   [a-zA-Z]

Digit
[. angka. ]  ~   [0-9]   ~   \d

Karakter Alfanumerik (Huruf atau Angka)
[. alnum. ]   ~   [a-zA-Z0-9]

Karakter Kata (Huruf, Angka, dan Garis Bawah)
[. kata. ] ~   \w

Tanda Baca (Karakter/Simbol Khusus))
[. tusukan. ]

Karakter Terlihat (Tanpa Spasi)
[. grafik. ]

Karakter yang Terlihat (Termasuk Spasi)
[. mencetak. ]

Karakter Whitespace (Spasi, Tab, dll. )
[. ruang angkasa. ]  ~   \s

Termasuk tanda tambah (+) dengan kelas karakter

Juga, SANGAT penting untuk dicatat bahwa saat menggunakan REGEXEXTRACT, jika Anda ingin menampilkan lebih dari satu karakter dalam hasil yang diekstraksi, Anda harus memberi tanda plus setelah ekspresi reguler, seperti \d+, atau [[. angka. ]]+

Jika Anda tidak menyertakan tanda tambah setelah ekspresi, hanya satu karakter yang mungkin muncul di keluaran (yang mungkin Anda inginkan dalam beberapa kasus)

Namun jika Anda ingin menampilkan lebih dari satu karakter dalam hasil Anda, sebaiknya sertakan tanda plus dengan ekspresi Anda. Bahkan dalam situasi saat menggunakan REGEXREPLACE, di mana Anda tidak selalu HARUS menyertakan tanda plus untuk menghasilkan lebih dari satu karakter, itu tidak akan berdampak negatif pada rumus Anda untuk memasukkannya.

Termasuk ruang dengan kelas karakter

Menyertakan spasi di lokasi yang benar dalam ekspresi tertentu dapat membuat perbedaan besar dalam keluaran yang dihasilkan oleh rumus… di mana menyertakan spasi akan menunjukkan bahwa spasi harus berupa karakter yang disertakan dalam ekspresi

Misalnya, rumus =REGEXREPLACE(C8,"[^a-zA-Z]", "") hanya akan mengembalikan teks, tanpa spasi. Namun rumus =REGEXREPLACE(C8,"[^a-zA-Z ]", "") yang memiliki spasi yang ditambahkan sebelum tanda kurung tutup, akan mengembalikan teks apa pun, termasuk spasi

Saat menyertakan spasi dalam ekspresi yang memiliki satu set tanda kurung, spasi berada di bagian dalam tanda kurung siku kanan (seperti yang ditampilkan di atas)

Saat menambahkan spasi ke ekspresi yang memiliki tanda kurung ganda… (tidak seperti tanda tambah yang disebutkan sebelumnya yang berada di luar kedua tanda kurung) spasi berada di antara dua tanda kurung di sisi kanan, seperti ini [[. angka. ] ]

Termasuk wortel (^) dengan kelas karakter

Dalam banyak kasus ketika mencoba untuk menetapkan kumpulan karakter yang benar, Anda perlu menggunakan simbol wortel (^) untuk mencocokkan karakter yang TIDAK ada dalam kelas karakter tertentu

Misalnya, untuk menunjuk karakter apa pun yang berupa angka, Anda akan menggunakan ekspresi [[. angka. ]], tetapi jika Anda ingin menetapkan semua karakter yang BUKAN angka (yang mencakup teks dan karakter khusus), Anda akan memasukkan wortel ke dalam ekspresi, seperti ini [^[. angka. ]]

Saat menggunakan wortel dengan ekspresi yang memiliki tanda kurung ganda, wortel berada di antara dua tanda kurung di sisi kiri (seperti yang ditunjukkan di atas)

Saat menggunakan wortel dengan ekspresi yang memiliki satu set tanda kurung, wortel berada di bagian dalam tanda kurung kiri, seperti ini [^0-9]

Untuk versi singkat dari kelas karakter, alih-alih menggunakan wortel, huruf dalam ekspresi diubah sederhana dari huruf kecil menjadi huruf besar, seperti (\d) (\D)

*Ingatlah untuk menggunakan garis miring ke belakang (\) dengan kelas steno, bukan garis miring ke depan

Teks vs. efek format angka

Untuk sebagian besar rumus dalam artikel ini, data sumber TIDAK boleh dalam format angka agar rumus berfungsi dengan baik. Ini biasanya default ketika Anda membuka lembar baru dan memasukkan data, dan seharusnya tidak menjadi masalah untuk string apa pun yang sudah memiliki nilai non-angka di dalamnya… namun dengan string yang hanya berisi angka, "angka" itu mungkin

Saat mencoba mengekstrak dari rangkaian angka yang dimasukkan ke dalam sel yang berformat angka sebenarnya (biasanya menyebabkan angka sejajar ke kanan), rumus biasanya akan menghasilkan kesalahan. Anda dapat melihat ini di banyak contoh di seluruh artikel ini, di baris 12, di mana string "123456789" dicantumkan dalam format angka dan hampir selalu menyebabkan rumus menampilkan pesan kesalahan. Jika rangkaian angka ini dalam format teks biasa (yang akan menyebabkannya rata ke kiri), maka banyak rumus akan benar-benar berfungsi pada string ini daripada memberikan pesan kesalahan

Menampilkan/memahami batasan formula ini adalah bagian penting lainnya untuk memahami cara mengekstrak di Google Sheets

Mengekstrak teks, angka, dll. di Google Spreadsheet

Jadi, mari kita mulai mempelajari berbagai macam rumus yang dapat Anda gunakan untuk mengekstrak dengan berbagai cara di Google Sheets

Ekstrak angka ke dalam kolom terpisah

Pertama saya akan menunjukkan cara mengekstrak angka dari string dengan menggunakan fungsi SPLIT, di mana setiap substring dari angka berurutan yang ditemukan dalam string asli akan ditampilkan/diproyeksikan ke kolom individual. Dengan kata lain, Anda hanya akan mendapatkan angka dalam hasil Anda, tetapi mereka akan dibagi menjadi kolom individual di mana setiap kemunculan non-angka ditemukan.

Jika Anda ingin menggunakan fungsi SPLIT untuk mengekstrak angka tetapi ingin mengumpulkan angka menjadi satu kolom, (jika Anda menyukai fungsi SPLIT dan belum terbiasa dengan beberapa rumus di bawah), maka Anda dapat menggabungkan kolom dari

Saat menggunakan fungsi SPLIT untuk mengekstrak nilai yang kita inginkan, kita harus menyatakan nilai yang TIDAK kita inginkan di dalam rumus, jadi saat mengekstraksi angka, ini berarti kita harus memasukkan semua karakter teks ke dalam kriteria rumus (serta

Karena untuk tujuan ini huruf kecil dan huruf kapital diperlakukan berbeda, kami harus menyertakan teks versi huruf kecil dan besar dalam kriteria kami, untuk memastikan bahwa kami hanya mengekstraksi angka

Untuk melakukan ini, kita dapat mengetikkan versi huruf kecil/besar dari setiap huruf secara manual, ATAU kita dapat membungkus rentang sumber dalam fungsi LOWER, sehingga kita dapat dengan mudah menyertakan versi huruf kecil dari huruf dalam kriteria. Dalam contoh kami telah menggunakan fungsi LOWER, tetapi saya juga menyertakan versi tanpanya sebagai referensi, di bawah

Tugas. Ekstrak angka hanya dari string teks dan angka, dan pisahkan angka berurutan menjadi kolom terpisah

Logika. Pisahkan sel dalam rentang A3. A12, dengan teks atau karakter tanda baca apa pun. Bungkus fungsi LOWER di sekitar rentang sumber untuk memastikan bahwa huruf kapital tidak diabaikan

Formula. Rumus di bawah ini, dimasukkan dalam sel biru di rentang C3. C12, untuk contoh ini

=SPLIT(bawah(A3),"qwertyuiopasdfghjklzxcvbnm`-=[]\;',. /[email dilindungi]#$%^&*()")

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Cara lain menuliskan rumus pada contoh di atas

=SPLIT(A3,"qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM`-=[]\;',. /[email dilindungi]#$%^&*()")

Ekstrak teks ke dalam kolom terpisah

Disini kita akan menggunakan kembali fungsi SPLIT seperti pada contoh diatas, namun kali ini kita akan mengekstrak teks bukan angka

Kali ini ada jauh lebih sedikit karakter yang harus diketik ke dalam kriteria SPLIT, karena angkanya jauh lebih sedikit daripada jumlah huruf

Tugas. Ekstrak teks hanya dari string teks dan angka, dan pisahkan karakter teks berurutan menjadi kolom terpisah

Logika. Pisahkan sel dalam rentang A3. A12, dengan nomor atau karakter tanda baca apa pun

Formula. Rumus di bawah ini, dimasukkan dalam sel biru di rentang C3. C12, untuk contoh ini

=SPLIT(A3,"1234567890`-=[]\;',. /[email dilindungi]#$%^&*()")

Ekstrak N karakter mulai dari Karakter N

Sebelum kita mulai mengekstrak string penuh teks/angka dll, mari kita bahas fungsi MID

Fungsi MID di Google Sheets akan memungkinkan Anda mengekstrak sejumlah karakter tertentu dari sebuah string, mulai dari karakter tertentu

Deskripsi fungsi MID Google Sheets

Sintaksis
MID(string, starting_at, extract_length)

Ringkasan rumus. “Mengembalikan segmen string. ”

Dalam contoh ini kita akan mengekstrak karakter ketiga dari daftar string karakter. Seperti disebutkan di atas, ini adalah data sumber mentah yang akan kami gunakan di setiap contoh untuk sisa artikel ini

Tugas. Ekstrak karakter ketiga dari setiap sel/string

Logika. Ekstrak satu karakter, mulai dari karakter ketiga, dari string di setiap sel dalam rentang A3. A12

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=TENGAH(A3,3,1)

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Formula serupa

• =MID((REGEXREPLACE(A3,"[^[. angka. ]]", "")),3,1) – Mengekstrak angka N yang dimulai dari angka ke-N
• =MID((REGEXREPLACE(A3,"[^0-9]", "")),3,1) – Mengekstrak angka N yang dimulai dari angka ke-N
• =MID((REGEXREPLACE(A3,"\D", "")),3,1) – Mengekstrak angka N yang dimulai dari angka ke-N
• =MID((REGEXREPLACE(A3,"[[. angka. ]]", "")),3,1) – Mengekstrak N bilangan bukan bilangan mulai dari bilangan bukan bilangan ke-N
• =MID((REGEXREPLACE(A3,"[0-9]", "")),3,1) – Mengekstrak N bilangan bukan bilangan mulai dari bilangan bukan bilangan ke-N
• =MID((REGEXREPLACE(A3,"\d", "")),3,1) – Mengekstrak N bilangan bukan bilangan mulai dari bilangan bukan bilangan ke-N
• =MID((REGEXREPLACE(A3,"[^[. alfa. ]]", "")),3,1) – Mengekstrak huruf N yang dimulai dari huruf ke-N
• =MID((REGEXREPLACE(A3,"[^a-zA-Z]", "")),3,1) – Mengekstrak N huruf yang dimulai dari huruf ke-N
• =MID((REGEXREPLACE(A3,"[[. alfa. ]]", "")),3,1) – Mengekstrak N bukan huruf mulai dari N bukan huruf
• =MID((REGEXREPLACE(A3,"[a-zA-Z]", "")),3,1) – Mengekstrak N non-huruf yang dimulai dari non-huruf ke-N
• =MID((REGEXREPLACE(A3,"[[. alnum. ]]", "")),3,1) – Mengekstrak N karakter tanda baca yang dimulai dari karakter tanda baca ke-N (termasuk spasi)
• =MID((REGEXREPLACE(A3,"[a-zA-Z0-9]", "")),3,1) – Mengekstrak N karakter tanda baca mulai dari karakter tanda baca ke-N (termasuk spasi)
• =MID((REGEXREPLACE(A3,"[^[. tusukan. ]]", "")),3,1) – Mengekstrak N karakter tanda baca yang dimulai dari karakter tanda baca ke-N (spasi tidak disertakan)
• =MID((REGEXREPLACE(A3,"[[. kata. ]]", "")),3,1) – Mengekstrak N karakter tanda baca yang dimulai dari karakter tanda baca ke-N (spasi disertakan tetapi bukan garis bawah)
• =MID((REGEXREPLACE(A3,"\w", "")),3,1) – Mengekstrak N karakter tanda baca yang dimulai dari karakter tanda baca ke-N (spasi disertakan tetapi bukan garis bawah)
• =MID((REGEXREPLACE(A3,"[[. tusukan. ]]", "")),3,1) – Mengekstrak N karakter non-tanda baca yang dimulai dari karakter non-tanda baca ke-N (termasuk spasi)
• =MID((REGEXREPLACE(A3,"[^[. alnum. ]]", "")),3,1) – Mengekstrak N karakter non-tanda baca yang dimulai dari karakter non-tanda baca ke-N (spasi tidak disertakan)
• =MID((REGEXREPLACE(A3,"[^a-zA-Z0-9]", "")),3,1) – Mengekstrak N karakter non-tanda baca mulai dari karakter non-tanda baca ke-N (spasi tidak disertakan)
• =MID((REGEXREPLACE(A3,"[^[. kata. ]]", "")),3,1) – Mengekstrak N karakter non-tanda baca yang dimulai dari karakter non-tanda baca ke-N (spasi/tanda hubung tidak disertakan tetapi garis bawah disertakan)
• =MID((REGEXREPLACE(A3,"\W", "")),3,1) (spasi/tanda hubung tidak disertakan tetapi garis bawah disertakan)

Ekstrak karakter yang tersisa mulai dari karakter ke-N

Dalam contoh ini kita akan menggunakan fungsi MID bersama dengan fungsi LEN, untuk mengekstrak sisa karakter dalam sebuah string yang dimulai dari karakter/posisi tertentu

Perhatikan bahwa untuk string yang memiliki kurang dari 11 karakter, rumus akan menghasilkan string kosong

Tugas. Ekstrak karakter yang tersisa dari setiap sel/string, mulai dari karakter ke-11

Logika. Mulai dari karakter ke-11, ekstrak karakter yang tersisa dari setiap sel dalam rentang A3. A12

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=MID(A3,11,LEN(A3))

Ekstrak angka dari string di Google Sheets

Sekarang kita akhirnya akan mulai menggunakan fungsi REGEXREPLACE, untuk mengekstrak seluruh string teks, angka, dan tipe karakter tertentu lainnya

Untuk penjelasan mendalam tentang cara menggunakan fungsi REGEXREPLACE dan REGEXEXTRACT, kembali ke bagian atas halaman untuk mendapatkan banyak informasi. Tapi di sini kita hanya akan menggunakan fungsi dalam berbagai cara

Dalam contoh ini saya akan menunjukkan cara mengekstrak angka dari string di Google Sheets, dengan mengganti karakter apa pun yang bukan angka, dengan kosong/ string kosong

*Dalam contoh khusus ini kami juga menggunakan fungsi VALUE, untuk memastikan bahwa angka yang kami ekstrak dalam format angka

Meskipun kami menampilkan angka dalam keluaran rumus kami, rumus mengharapkan teks berada di masukan, jadi perhatikan bahwa di baris 12 rumus menghasilkan kesalahan karena masukan untuk entri itu hanya angka… tetapi lebih khusus dalam format angka . Jika string yang persis sama ini (123456789) hanya diubah menjadi format teks biasa, rumus akan menampilkan seluruh string

Tugas. Ekstrak angka dari setiap sel/string

Logika. Ekstrak angka dari setiap sel dalam rentang A3. A12, dengan mengganti sembarang non-digit dengan string kosong

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=VALUE(REGEXREPLACE(A3,"[^[. angka. ]]", ""))

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Cara lain menuliskan rumus pada contoh di atas

• =VALUE(REGEXREPLACE(A3,"[^0-9]", ""))
• =VALUE(REGEXREPLACE(A3,"\D", ""))

Formula serupa

• =REGEXEXTRACT(A3, "(\d+\. ?\d+)") – Mengekstrak angka dengan desimal
• =REGEXREPLACE(A3,"[[. angka. ]]", "") – Mengekstrak non-angka
• =REGEXREPLACE(A3,"[0-9]", "") – Mengekstrak bukan angka
• =REGEXREPLACE(A3,"\d", "") – Mengekstrak bukan angka

Ekstrak teks dari string di Google Sheets

Sekarang setelah Anda mengetahui cara mengekstrak angka dengan menggunakan fungsi REGEXREPLACE, perubahan sederhana pada kelas karakter/ekspresi reguler sekarang akan memungkinkan kita mengekstrak semua jenis karakter yang berbeda

Dalam contoh ini, saya akan menunjukkan cara mengekstrak teks dari string di Google Sheets

Perhatikan bahwa saat menggunakan rumus ini pada string yang tidak berisi teks, rumus tersebut akan menghasilkan string kosong

Tugas. Ekstrak teks dari setiap sel/string

Logika. Ekstrak teks dari setiap sel dalam rentang A3. A12, dengan mengganti karakter non-teks dengan string kosong

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=REGEXREPLACE(A3,"[^[. alfa. ]]", "")

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Cara lain menuliskan rumus pada contoh di atas

• =REGEXREPLACE(A3,"[^a-zA-Z]", "")

Formula serupa

• =REGEXREPLACE(A3,"[[. alfa. ]]", "") – Mengekstrak karakter non-teks
• =REGEXREPLACE(A3,"[a-zA-Z]", "") – Mengekstrak karakter non-teks

Cara menghapus tanda baca dari string di Google Sheets

Sekarang saya akan menunjukkan cara menghapus tanda baca dari string di Google Sheets, atau dengan kata lain cara mengekstrak karakter non-tanda baca

Tugas. Hapus tanda baca dari setiap sel/string

Logika. Hapus tanda baca dari setiap sel dalam rentang A3. A12, dengan mengganti karakter non-tanda baca dengan string kosong

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=REGEXREPLACE(A3,"[[. tusukan. ]]", "")

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Formula serupa

• =REGEXREPLACE(A3,"[^[. alnum. ]]", "") – Menghilangkan tanda baca (dan spasi)
• =REGEXREPLACE(A3,"[^a-zA-Z0-9]", "") – Menghilangkan tanda baca (dan spasi)
• =REGEXREPLACE(A3,"[^[. kata. ]]", "") – Menghilangkan tanda baca (dan spasi, tetapi bukan garis bawah)
• =REGEXREPLACE(A3,"\W", "") – Menghapus tanda baca (dan spasi, tetapi bukan garis bawah)
• =REGEXREPLACE(A3,"[[. alnum. ]]", "") – Mengekstrak tanda baca (termasuk spasi)
• =REGEXREPLACE(A3,"[a-zA-Z0-9]", "") – Mengekstrak tanda baca (termasuk spasi)
• =REGEXREPLACE(A3,"[^[. tusukan. ]]", "") – Mengekstrak tanda baca (spasi tidak termasuk)
• =REGEXREPLACE(A3,"[[. kata. ]]", "") – Mengekstrak tanda baca (spasi disertakan tetapi bukan garis bawah)
• =REGEXREPLACE(A3,"\w", "") – Mengekstrak tanda baca (spasi disertakan tetapi bukan garis bawah)

Ekstrak karakter sebelum akhiran- Bagian 1

Dalam contoh ini saya akan menunjukkan cara mengekstrak karakter yang ditemukan sebelum akhiran. Karena kita menggunakan bermacam-macam data sumber yang sama di setiap contoh, perhatikan bahwa tidak semua string berisi akhiran yang kita cari dalam contoh ini.

Perhatikan bahwa baris 4, 5, dan 10 adalah satu-satunya entri/baris yang berisi akhiran "Kode", dan inilah mengapa rumus khusus ini hanya akan berfungsi pada entri ini

Tugas. Ekstrak karakter sebelum akhiran, dari setiap sel/string

Logika. Ekstrak serangkaian karakter sebelum akhiran "Kode", dari setiap sel dalam rentang A3. A12, dengan menentukan akhiran setelah kelas karakter, dalam ekspresi reguler REGEXEXTRACT

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=REGEXEXTRACT(A3,"([[. mencetak. ]]+)Kode")

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Formula serupa

• =REGEXEXTRACT(A3,"([[. grafik. ]]+)Kode") – Mengekstrak karakter sebelum akhiran (spasi tidak disertakan)

Ekstrak karakter sebelum akhiran- Bagian 2

Cara lain untuk mengekstrak karakter sebelum akhiran adalah dengan menggunakan fungsi LEFT dan SEARCH

Sama seperti pada contoh terakhir, hanya entri/baris yang benar-benar berisi akhiran "Kode" yang dapat digunakan dengan rumus ini

Tugas. Ekstrak karakter yang ditemukan sebelum akhiran tertentu, dari setiap sel/string

Logika. Ekstrak serangkaian karakter sebelum akhiran "Kode", dari setiap sel dalam rentang A3. A12, dengan menggunakan fungsi SEARCH untuk menemukan posisi akhiran dan karenanya memberikan jumlah karakter yang akan diekstraksi dengan fungsi LEFT

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=LEFT(A3, SEARCH("Kode",A3)-1)

Ekstrak kata pertama dari sel- Bagian 1

Anda mungkin menemukan situasi di mana Anda perlu mengekstrak nama depan/kata dari sel Google Sheets, jadi di sini saya akan menunjukkan cara melakukannya dengan menggunakan fungsi REGEXEXTRACT

Tugas. Ekstrak nama depan dari setiap sel/string

Logika. Ekstrak kata/nama pertama (rangkaian karakter pertama sebelum spasi), dari setiap sel dalam rentang A3. A12, dengan mengekstrak string karakter non-spasi yang ditemukan sebelum spasi pertama

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=REGEXEXTRACT(A3,"[^[. ruang angkasa. ]]+")

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Cara lain menuliskan rumus pada contoh di atas

• =REGEXEXTRACT(A3,"\S+")
• =REGEXEXTRACT(A3,"[[. grafik. ]]+")

Formula serupa

• =REGEXEXTRACT(A3,"[[. angka. ]]+") – Mengekstrak string angka pertama
• =REGEXEXTRACT(A3,"[0-9]+") – Mengekstrak string angka pertama
• =REGEXEXTRACT(A3,"\d+") – Mengekstrak string angka pertama
• =REGEXEXTRACT(A3,"[^[. angka. ]]+") – Mengekstrak string bukan angka pertama
• =REGEXEXTRACT(A3,"[^0-9]+") – Mengekstrak string bukan angka pertama
• =REGEXEXTRACT(A3,"\D+") – Mengekstrak string bukan angka pertama
• =REGEXEXTRACT(A3,"[[. alfa. ]]+") – Mengekstrak string teks pertama
• =REGEXEXTRACT(A3,"[a-zA-Z]+") – Mengekstrak string teks pertama
• =REGEXEXTRACT(A3,"[^[. alfa. ]]+") – Mengekstrak string non-teks pertama
• =REGEXEXTRACT(A3,"[^a-zA-Z]+") – Mengekstrak string non-teks pertama
• =REGEXEXTRACT(A3,"[[. alnum. ]]+") – Mengekstrak string non-tanda baca pertama (spasi tidak disertakan)
• =REGEXEXTRACT(A3,"[a-zA-Z0-9]+") – Mengekstrak string non-tanda baca pertama (spasi tidak disertakan)
• =REGEXEXTRACT(A3,"[^[. tusukan. ]]+") – Mengekstrak string non-tanda baca pertama (termasuk spasi)
• =REGEXEXTRACT(A3,"[[. kata. ]]+") – Mengekstrak string non-tanda baca pertama (spasi/tanda hubung tidak disertakan tetapi garis bawah disertakan)
• =REGEXEXTRACT(A3,"\w+") – Mengekstrak string non-tanda baca pertama (spasi/tanda hubung tidak disertakan tetapi garis bawah disertakan)
• =REGEXEXTRACT(A3,"[^[. alnum. ]]+") – Mengekstrak string tanda baca pertama (termasuk spasi)
• =REGEXEXTRACT(A3,"[^a-zA-Z0-9]+") – Mengekstrak string tanda baca pertama (termasuk spasi)
• =REGEXEXTRACT(A3,"[[. tusukan. ]]+")- Mengekstrak string tanda baca pertama (spasi tidak disertakan)
• =REGEXEXTRACT(A3,"[^[. kata. ]]")- Mengekstrak string tanda baca pertama (garis bawah tidak disertakan)
• =REGEXEXTRACT(A3,"\W+")- Mengekstrak string tanda baca pertama (garis bawah tidak disertakan)

Ekstrak nama depan / kata- Bagian 2

Dalam contoh ini saya akan menunjukkan cara lain untuk mengekstrak nama depan/kata di Google Sheets, dengan menggunakan fungsi LEFT dan FIND. Ini akan menampilkan rangkaian karakter pertama yang muncul sebelum spasi pertama

Perhatikan bahwa rumus ini hanya akan berfungsi pada string yang memiliki spasi di dalamnya. Perhatikan juga bahwa di baris 5 di mana spasi adalah karakter/posisi pertama dalam sebuah string, rumus menghasilkan string kosong

Tugas. Ekstrak kata pertama dari setiap sel/string

Logika. Ekstrak kata pertama (mis. e. nama) dari setiap sel dalam rentang A3. A12, dengan menggunakan fungsi FIND untuk menyediakan kriteria fungsi LEFT

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=KIRI(A3,CARI(" ",A3)-1)

Ekstrak karakter pertama dari sebuah string

Dalam contoh ini saya akan menunjukkan cara mengekstrak karakter pertama dari string di Google Sheets. Anda akan melihat bahwa rumus ini hampir identik dengan rumus yang digunakan sebelumnya di artikel untuk mengekstrak nama depan… tetapi perhatikan bahwa dalam contoh ini tidak ada tanda plus yang digunakan di kelas karakter, yang berarti hanya satu karakter yang akan digunakan.

Tugas. Ekstrak karakter pertama dari setiap sel/string

Logika. Ekstrak karakter pertama dari setiap sel dalam rentang A3. A12, dengan mengekstraksi karakter bukan spasi pertama dengan fungsi REGEXEXTRACT (tanpa menggunakan tanda tambah pada kelas karakter)

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=REGEXEXTRACT(A3,"[^[. ruang angkasa. ]]")

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Cara lain menuliskan rumus pada contoh di atas

=REGEXEXTRACT(A3,"[[. grafik. ]]")

=REGEXEXTRACT(A3,"\S")

Formula serupa

=REGEXEXTRACT(A3,"[[. mencetak. ]]") – Ekstrak karakter pertama (termasuk spasi)

Ekstrak nama belakang dari sel

Dalam contoh ini, saya akan menunjukkan kepada Anda rumus yang dapat Anda gunakan untuk mengekstrak nama belakang dari sel di Google Sheets

Perhatikan bahwa rumus ini hanya akan berfungsi pada string/entri yang berisi spasi di dalamnya

Tugas. Ekstrak nama belakang dari setiap sel/string

Logika. Ekstrak nama belakang dari setiap sel dalam rentang A3. A12, dengan menggunakan fungsi berikut. KANAN, LEN, TEMUKAN, dan GANTI

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=KANAN(A3,LEN(A3)-FIND("*",GANTI(A3," ","*",LEN(A3)-LEN(GANTI(A3," ","")))))

Ekstrak kata ke-N di Google Sheets

Jadi kita telah membahas rumus yang mengekstrak kata pertama atau terakhir dari sel… tetapi jika Anda ingin menentukan kata yang ingin Anda ekstrak di Google Sheets, Anda dapat melakukannya dengan menggunakan fungsi INDEX dan SPLIT

Perhatikan bahwa untuk string yang tidak memiliki spasi di dalamnya, rumus ini akan mengekstrak seluruh isi sel. Perhatikan juga bahwa dengan rumus ini, di baris 5 bahwa meskipun spasi berada di posisi pertama string… kata/string pertama dari karakter sebenarnya tetap ditemukan dan ditampilkan (di mana pada contoh sebelumnya spasi utama ini menyebabkan rumus yang berbeda

Tugas. Ekstrak kata pertama dari setiap sel/string

Logika. Ekstrak kata pertama dari setiap sel dalam rentang A3. A12, dengan memisahkan string dengan spasi, dan mengekstraksi sel pertama dari hasil pemisahan

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=INDEX(SPLIT(A3, ""),1)

Ekstrak N Karakter dari kiri/kanan string

Dalam contoh ini, kami akan mengekstrak sejumlah karakter tertentu dari sisi kiri string, dengan menggunakan fungsi LEFT

Fungsi LEFT di Google Sheets akan menampilkan substring dengan jumlah karakter tertentu, mulai dari awal string yang Anda tentukan

Deskripsi fungsi Google Sheets LEFT

Sintaksis
KIRI(string, [jumlah_karakter])

Ringkasan rumus. “Mengembalikan substring dari awal string yang ditentukan. ”

Tugas. Ekstrak 2 karakter dari sisi kiri setiap sel/string

Logika. Ekstrak 2 karakter dari kiri setiap sel dalam rentang A3. A12, dengan menggunakan fungsi LEFT

Formula. Rumus di bawah ini, dimasukkan dalam sel biru. Ini awalnya ke dalam sel C3, lalu disalin/diisi ke dalam rentang C3. C12

=KIRI(A3,2)

Lebih banyak formula

Di bawah ini adalah lebih banyak rumus yang melakukan tugas yang serupa/persis sama dengan rumus yang ditunjukkan dalam contoh di atas

Formula serupa

• =RIGHT(A3,2) – Mengekstrak N karakter di sebelah kanan string
• =LEFT(REGEXREPLACE(A3,"\D+", ""),2)) – Mengekstrak N angka di sebelah kiri string
• =RIGHT(REGEXREPLACE(A3,"\D+", ""),2)) – Mengekstrak angka N di sebelah kanan string
• =LEFT(REGEXREPLACE(A3,"\d+", ""),2)) – Mengekstrak N huruf di sebelah kiri string
• =RIGHT(REGEXREPLACE(A3,"\d+", ""),2)) – Mengekstrak N huruf di sebelah kanan string

Kuis pop. Uji pengetahuan Anda

Jawablah pertanyaan di bawah ini tentang penggalian, untuk menyempurnakan pengetahuan Anda. Gulir ke bagian paling bawah untuk menemukan jawaban kuis

Unduhan ruang kelas

Lembar contekan rumus ekstraksi (PDF)

Pertanyaan 1

Manakah dari rumus berikut yang akan mengekstraksi teks?

1. =VALUE(REGEXREPLACE(A1,"[^[. angka. ]]", ""))
2. =REGEXREPLACE(C1,"[^[. alfa. ]]", "")
3. =REGEXREPLACE(G1,"[[. tusukan. ]]", "")

Pertanyaan #2

Manakah dari rumus berikut yang akan mengekstrak angka?

1. =REGEXREPLACE(Z11,"[[. tusukan. ]]", "")
2. =REGEXREPLACE(J7,"[^[. alfa. ]]", "")
3. =VALUE(REGEXREPLACE(P17,"[^[. angka. ]]", ""))

Pertanyaan #3

Benar atau salah. Fungsi REGEXREPLACE dapat digunakan untuk mengekstrak/mengganti SETIAP instance dari tipe karakter tertentu, di mana fungsi REGEXEXTRACT dapat digunakan untuk mengekstrak bagian "substring" dari string sumber