Teknik Substring Excel: Ekstrak dan Format Teks

Mengekstrak Teks berdasarkan Posisi Menggunakan Fungsi Substring Excel

Sering kali kita ingin mengekstrak bagian tertentu dari sebuah string teks, seperti sebuah kata atau urutan karakter, berdasarkan posisinya. Excel menyediakan beberapa fungsi bawaan untuk membantu Anda mengekstrak bagian tertentu dari string teks. Beberapa fungsi yang paling umum adalah LEFT(), RIGHT(), MID(), TEXTBEFORE(), dan TEXTAFTER(). Mari kita lihat masing-masing. 

Menggunakan fungsi substring Excel LEFT()

Anda dapat menggunakan fungsi LEFT() untuk mengekstrak karakter dari awal string teks. Berikut sintaksnya:

=LEFT(text, [num_chars])

Berikut penjelasannya: 

• text mengacu pada alamat sel tempat string teks asli berada.

• num_chars mengacu pada jumlah karakter yang akan diekstrak. Nilai defaultnya adalah 1.

Mari lihat contoh. Di sini, saya memiliki beberapa kode produk, dan saya ingin mengekstrak tiga karakter pertama darinya. 

=LEFT(A2,3)

Ambil tiga karakter pertama menggunakan fungsi LEFT(). Gambar oleh Penulis.

Menggunakan fungsi substring Excel RIGHT()

Fungsi RIGHT() di Excel mengekstrak karakter dari akhir string teks. Berikut sintaksnya:

=Right(text, [num_chars])

Berikut penjelasannya: 

• text mengacu pada alamat sel tempat string teks asli berada.

• num_chars mengacu pada jumlah karakter yang akan diekstrak. Nilai defaultnya adalah 1.

Mari lihat contoh. Di sini, saya menggunakan fungsi RIGHT() untuk mengekstrak tiga karakter terakhir.

=RIGHT(A2, 3)

Ambil tiga karakter terakhir menggunakan fungsi RIGHT(). Gambar oleh Penulis.

Menggunakan fungsi substring Excel MID()

Fungsi MID() mengekstrak karakter dari tengah string teks. Berikut sintaksnya:

=MID(text, start_num, num_chars)

Berikut penjelasannya:

• text mengacu pada alamat sel tempat string teks asli berada.

• num_chars mengacu pada jumlah karakter yang akan diekstrak. Nilai defaultnya adalah 1.

• start_num mengacu pada posisi awal di dalam string teks.

Pada contoh ini, saya menggunakan rumus MID() untuk mengekstrak nilai di tengah string mulai dari karakter keempat.

=MID(A2,4,3)

Ambil tiga karakter di tengah menggunakan fungsi MID(). Gambar oleh Penulis. 

Selesai. =MID(A2,4,3) mengekstrak tiga karakter dari teks di sel A2, mulai dari karakter ke-4.

Menggunakan fungsi substring Excel FIND()

Fungsi FIND() di Excel menemukan sebuah substring di dalam string teks dan mengembalikan posisi karakter pertama dari substring tersebut di dalam string. Berikut sintaksnya:

FIND(find_text, within_text, [start_num]) 

Berikut penjelasannya:

• find_text mengacu pada teks yang ingin kita cari.

• within_text adalah teks yang memuat teks yang ingin kita temukan.

• start_num menentukan posisi untuk memulai pencarian, dengan nilai default 1.

Mari pertimbangkan dataset sederhana untuk menunjukkan cara kerja fungsi FIND() di Excel. Pada contoh ini, saya memiliki nama DataCamp di kolom Text, dan saya ingin menemukan posisi substring Camp. Ini mengembalikan 5 karena Camp dimulai pada karakter kelima dalam teks.

=FIND("Camp", A2)

Menggunakan fungsi FIND() untuk mengekstrak posisi teks. Gambar oleh Penulis.

Menggunakan fungsi substring Excel SUBSTITUTE()

Fungsi SUBSTITUTE() mengganti substring tertentu dengan yang lain. Berikut sintaksnya:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Berikut penjelasannya:

• text berisi teks yang ingin Anda ubah.

• old_text mengacu pada teks yang ingin Anda ganti.

• new_text adalah teks pengganti untuk teks lama.

• instance_num menentukan kemunculan keberapa dari teks yang ingin Anda ubah. Jika tidak ditentukan, semua kemunculan akan diganti. 

Untuk memahaminya, mari lihat dataset sederhana yang berisi string teks dengan pemisah tertentu. Kolom Text mencantumkan nama dan usia yang diformat dengan titik koma sebagai pemisah. Untuk membersihkannya, saya mengetikkan rumus berikut:

=SUBSTITUTE(A2, ";" , ",")

Berikut penjelasannya:

• A2 mengacu pada sel yang berisi teks asli.

• ";" menentukan teks lama yang akan kita ubah.

• "," adalah teks pengganti untuk teks lama.

Menggunakan fungsi substitute untuk mengganti teks lama. Gambar oleh Penulis.

Selesai. Dengan menerapkan rumus kita di sel B2, Excel mengganti titik koma dengan koma.

Mengekstrak Teks berdasarkan Pemisah Menggunakan Fungsi Substring Excel

Delimiter atau pemisah adalah karakter khusus yang memisahkan dua string teks, seperti titik koma atau koma. Di Excel, Anda dapat menggunakan fungsi TEXTBEFORE() dan TEXTAFTER() untuk mengekstrak teks saat pemisah tersebut ada.  

Menggunakan fungsi substring Excel TEXTBEFORE() 

Sesuai namanya, fungsi TEXTBEFORE() mengekstrak teks yang berada sebelum karakter atau substring tertentu. Sintaksnya sebagai berikut:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Berikut penjelasannya: 

• text mengacu pada teks asli.

• delimiter biasanya berupa koma atau titik koma.

• instance_num adalah kemunculan pemisah yang diinginkan. Nilai defaultnya adalah 1.

• match_mode menunjukkan apakah pencarian pemisah peka huruf besar-kecil (TRUE, default) atau tidak peka huruf besar-kecil (FALSE).

• match_end menentukan apakah akhir string teks harus diperlakukan sebagai pemisah. Jika TRUE, fungsi akan mengembalikan teks asli jika pemisah tidak ditemukan.

• if_not_found menentukan nilai kustom yang akan dikembalikan jika pemisah tidak ditemukan.

Sekarang, mari ambil contoh untuk melihat rumus ini beraksi. Saya memiliki kolom Text yang berisi detail karyawan, termasuk nama, departemen, dan lokasi cabang mereka. Saya sekarang ingin mengekstrak hanya nama dan departemennya.

=TEXTBEFORE(A2, ",", 2, 1, 1)

Menggunakan fungsi TEXTBEFORE() untuk mengekstrak data. Gambar oleh Penulis 

Begini cara kerjanya: 

• A2 berisi teks yang ingin saya ekstrak. 

• "," adalah pemisah yang membagi teks pada data asli saya. 

• 2 berarti fungsi akan mempertimbangkan kemunculan kedua dari pemisah.

• 1 mengabaikan perbedaan huruf besar-kecil, jika ada.

• 1 (di bagian akhir) mengembalikan teks asli jika pemisah tidak ada.

Menggunakan fungsi substring Excel TEXTAFTER()

Fungsi TEXTAFTER() mirip dengan fungsi TEXTBEFORE()—perbedaannya hanya pada hasilnya. Fungsi TEXTAFTER() mengekstrak teks yang muncul setelah pemisah. Sintaksnya sebagai berikut:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Berikut penjelasannya: 

• text mengacu pada teks asli.

• delimiter adalah koma atau titik koma. 

• instance_num adalah kemunculan pemisah yang diinginkan. Nilai defaultnya adalah 1.

• match_mode menunjukkan apakah pencarian pemisah peka huruf besar-kecil (TRUE, default) atau tidak peka huruf besar-kecil (FALSE).

• match_end menentukan apakah akhir string teks harus diperlakukan sebagai pemisah. Jika TRUE, fungsi akan mengembalikan teks asli jika pemisah tidak ditemukan.

• if_not_found menentukan nilai kustom yang akan dikembalikan jika pemisah tidak ditemukan.

Di sini, saya memiliki kolom Text yang berisi detail karyawan, termasuk nama, departemen, dan lokasi cabang mereka. Dari sini, saya ingin mengekstrak lokasi dan ID karyawan. 

=TEXTAFTER(A2,",",2,1,1)

Menggunakan fungsi TEXTAFTER() untuk mengekstrak data. Gambar oleh Penulis 

Begini cara kerjanya: 

• A2 berisi teks yang ingin saya ekstrak. 

• ","  adalah pemisah yang membagi teks pada data asli saya. 

• 2 berarti fungsi akan mempertimbangkan kemunculan kedua dari pemisah. 

• 1 mengabaikan perbedaan huruf besar-kecil, jika ada.

• 1 (di bagian akhir) mengembalikan teks asli jika pemisah tidak ada.

Kasus Penggunaan Substring Excel Tertentu

Sekarang setelah Anda mengetahui dasar-dasar substring, saatnya mempelajari metode lanjutan. Metode ini memungkinkan manipulasi teks yang lebih kompleks, sehingga tugas pemrosesan data Anda menjadi lebih efisien.

Menangani panjang nama depan yang bervariasi

Saya memiliki dataset yang berisi nama lengkap, tetapi saya ingin mengekstrak hanya nama depannya.

Tabel yang berisi nama lengkap. Gambar oleh Penulis.

Anda mungkin berpikir ini sederhana—gunakan fungsi LEFT() untuk mendapatkan nama depan. Jadi, saya mencobanya menggunakan rumus:

=LEFT(A2,4)

Menggunakan fungsi LEFT() untuk mengambil nama depan. Gambar oleh Penulis.

Namun alih-alih mendapatkan nama depan, saya memperoleh empat karakter pertama dari nama tersebut karena saya menentukan num_char sebagai 4, melihat bahwa Jane memiliki empat karakter pada nama depannya. Saat saya salin-tempel rumusnya, hasilnya tidak sesuai untuk nama-nama lainnya.

Saya membutuhkan solusi yang lebih fleksibel. Untuk mengekstrak nama depan, terlepas dari panjangnya, Anda dapat menggabungkan fungsi LEFT() dengan fungsi FIND(). Saya menggunakan fungsi FIND() untuk menemukan posisi spasi pertama pada nama lengkap, yang memisahkan nama depan dan nama belakang. Lalu, saya menggunakan fungsi LEFT() untuk mengekstrak karakter hingga posisi tersebut. 

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

Gabungkan fungsi FIND() dan LEFT() untuk mengekstrak nama depan. Gambar oleh Penulis.

Mari saya uraikan rumusnya agar Anda memahami cara kerjanya:

• Sel A2 berisi nama lengkap.

• FIND(" ", A2, 1) menemukan posisi spasi pertama pada string teks di sel A2 mulai dari karakter pertama.

• -1 digunakan untuk menghapus spasi.

• LEFT(A2, ...) mengekstrak sejumlah karakter yang ditentukan dari sisi kiri string teks.

Mengekstrak domain dari alamat email

Mari ambil contoh lain untuk melihat apakah  LEFT() dan FIND() dapat menangani skenario berbeda. Saya memiliki dataset berbeda berisi alamat email yang harus saya ekstrak nama domainnya.

Data yang berisi alamat email. Gambar oleh Penulis.

Untuk melakukannya, saya membuat kolom berbeda, Email Domain, dan memilih sel berbeda untuk mengetik rumus berikut, yang kemudian saya salin ke tiga sel lainnya:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Mengekstrak nama domain menggunakan RIGHT(), LEN(), dan FIND(). Gambar oleh Penulis.

Ini dia—saya sudah memiliki semua nama domain. Namun mari pahami bagaimana Excel mendapatkan hasil yang saya inginkan:

• FIND("@", A2) menemukan posisi simbol @ pada alamat email.

• LEN(A2) menghitung total panjang alamat email.

• LEN(A2) - FIND("@", A2) menghitung jumlah karakter setelah simbol @.

• Terakhir, RIGHT(A2, LEN(A2) - FIND("@", A2)) mengekstrak sejumlah karakter tersebut dari sisi kanan string.

Ekstrak kode produk dengan menggabungkan LEFT(), MID(), dan RIGHT()

Sejauh ini, saya hanya menjelaskan bagaimana fungsi LEFT(), MID(), dan RIGHT() bekerja secara terpisah. Namun Anda bisa melakukan lebih banyak lagi dengan menggabungkan fungsi-fungsi ini. Fungsi tersebut dapat mengekstrak bagian berbeda dari sebuah string teks berdasarkan pola tertentu.

Di sini, saya memiliki daftar kode produk dan saya ingin mengekstrak bagian tertentu darinya. 

• Dari bagian pertama, saya ingin satu karakter.
• Dari bagian kedua, saya ingin semua empat karakter.
• Dari bagian terakhir, saya ingin mengekstrak tiga karakter terakhir.

Daftar kode produk. Gambar oleh Penulis.

Untuk melakukannya, saya menggabungkan fungsi LEFT(), RIGHT(), dan MID() dan memasukkan rumus berikut:  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Gabungkan fungsi MID(), LEFT(), dan RIGHT() untuk mengambil data. Gambar oleh Penulis.

Seperti yang Anda lihat, rumus gabungan ini mengekstrak karakter yang diinginkan dari setiap bagian dari kode. Begini caranya:

• LEFT(A2, 1) mengekstrak karakter pertama.

• MID(A2, FIND("-", A2) + 1, 4) menemukan tanda hubung pertama, bergeser satu karakter ke kanan, lalu mengekstrak empat karakter.

• "-" menambahkan tanda hubung.

• RIGHT(A2, 3) mengekstrak tiga karakter terakhir.

• & menggabungkan karakter-karakter tersebut. 

Menggunakan SUBSTITUTE() dengan MID() untuk ekstraksi teks dinamis 

Sekarang, mari lihat bagaimana Anda dapat menggabungkan fungsi MID() dengan fungsi SUBSTITUTE() untuk mengekstrak bagian tertentu dari string teks lalu mengganti karakter atau substring di dalam bagian yang diekstrak tersebut.

Sebagai contoh, saya memiliki Order Details pelanggan dan ingin mengganti ID pesanan dengan teks Order confirmed.

Tabel yang berisi detail pesanan. Gambar oleh Penulis.

Saya menggunakan rumus berikut:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

Fungsi SUBSTITUTE() dan MID() untuk mengganti teks. Gambar oleh Penulis.

Selesai! Anda dapat melihat rumus ini mengekstrak bagian tertentu dari string teks, seperti yang saya inginkan.

• MID(A2, 1, 19) mengekstrak 19 karakter pertama dari teks di sel A2.

• SUBSTITUTE(text, old_text, new_text) mengganti kemunculan old_text dengan new_text.

Mari lihat contoh lain: Di sini, saya memiliki kolom Product Detail, dan saya ingin mengekstrak ukuran produk dari tanda kurung siku.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Mengekstrak ukuran produk dari tanda kurung siku. Gambar oleh Penulis.

Selesai—semua detail berhasil diekstrak.

• FIND("[", A2) menemukan posisi awal tanda kurung siku pembuka.

• FIND("]", A2) menemukan posisi akhir tanda kurung siku penutup.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1) mengekstrak teks di antara kedua tanda kurung siku.

• SUBSTITUTE(...,"[", "") menghapus tanda kurung siku pembuka dari teks yang diekstrak.

Membersihkan entri data

Terkadang, data kita tidak teratur, tetapi bukan berarti Anda harus menggunakannya apa adanya. Di sini, saya memiliki daftar nomor telepon dalam berbagai format, tetapi saya ingin menstandarkannya ke format yang konsisten dengan membersihkan ketidakkonsistenan pada entri.

Nomor telepon yang tidak terformat. Gambar oleh Penulis.

Untuk menstandarkannya, saya membuat kolom berbeda bernama Clean data. Saya mengetik rumus berikut di sel kedua dan menyalinnya ke semua sel tempat saya ingin melihat hasilnya:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Berikut penjelasannya: 

• SUBSTITUTE(A2, "-", "") menghapus tanda hubung.

• SUBSTITUTE(..., "(", "") menghapus tanda kurung buka.

• SUBSTITUTE(..., ")", "") menghapus tanda kurung tutup.

• SUBSTITUTE(..., ".", "") menghapus titik.

Ini mengubah berbagai format menjadi rangkaian digit yang berkelanjutan, seperti di bawah. Sekarang, data sudah bersih, terformat rapi, dan siap digunakan. Anda bisa membiarkannya seperti itu jika mau.

Bersihkan data menggunakan fungsi SUBSTITUTE(). Gambar oleh Penulis.

Memformat teks untuk laporan

Jika Anda ingin melangkah lebih jauh setelah membersihkan data dan memformatnya dengan tepat, Anda juga dapat menggunakan berbagai fungsi substring.

Pertimbangkan contoh sebelumnya, saat saya membersihkan nomor telepon. Sekarang, saya ingin mengubah pemformatan nomor agar terlihat lebih seperti nomor telepon dan bukan sekadar rangkaian digit. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Format standar nomor menggunakan beberapa substring. Gambar oleh Penulis.

Selesai. Sekarang saya memiliki semua nomor yang saya inginkan. Begini cara kerja rumus ini:

• LEFT(B2, 3) mengekstrak tiga digit pertama.

• MID(B2, 4, 3) mengekstrak tiga digit berikutnya mulai dari posisi keempat .

• RIGHT(B2, 4) mengekstrak empat digit terakhir.

• TEXT(..., "000") dan TEXT(..., "0000") memformat setiap bagian untuk memastikan jumlah digit yang benar dengan nol di depan jika diperlukan.

• & "-" & menggabungkan bagian-bagian yang diformat dengan tanda hubung.

Pemikiran Akhir

Anda kini telah mempelajari cara menggunakan fungsi substring Excel seperti LEFT(), RIGHT(), MID(), TEXTBEFORE(), dan TEXTAFTER() untuk memanipulasi data teks. Baik untuk mengambil bagian tertentu dari sebuah string maupun membersihkan data yang berantakan, fungsi-fungsi ini dapat membuat pekerjaan Anda jauh lebih mudah dan efisien.

Namun, selalu ada hal baru untuk dipelajari di Excel. Kursus Introduction to Excel adalah langkah berikutnya yang tepat jika Anda baru memulai. Jika Anda ingin mempelajari lebih lanjut tentang analisis data, coba kursus Data Analysis in Excel yang cocok dipadukan dengan Data Manipulation in Excel Cheat Sheet kami. Ini akan membantu Anda membangun fondasi yang kuat dan membawa keterampilan Anda ke level berikutnya.