Rumus Excel untuk Mengekstrak Teks dari Cell

Excel adalah alat yang hebat, tetapi terkadang file spreadsheet yang kami kerjakan tidak ideal. Contoh klasik adalah Anda diberi file yang memiliki satu kolom data yang ingin Anda bagi menjadi beberapa. Itu adalah skenario yang saya hadapi ketika saya mendapatkan file dengan alamat jalan di satu bidang. Menggunakan fungsi teks LEFT, RIGHT, dan FIND dalam rumus, saya bisa meminta Excel mengekstrak substring ke bidang baru . (Termasuk lembar kerja latihan).

Apa itu Substring Excel?

Beberapa bahasa pemrograman memiliki fungsi string yang menangani substring. Excel melakukan hal serupa menggunakan fungsi Teks. Yang memiliki tempat sendiri di toolbar.

Saat kami mengatakan tentang substring, yang kami maksud adalah sebagian atau subset dari konten cell Excel . Misalnya, jika cell berisi 1001 Drake Ave, salah satu item berikut bisa menjadi substring:

  • 1001
  • Drake Ave.
  • 100
  • rake

Masalah Umum

Banyak database keanggotaan atau milis disiapkan dengan bidang yang ditentukan untuk Nama Depan, Nama Belakang, Jalan, Kota, Negara Bagian, dan Kode Pos. Format ini berfungsi dengan baik jika Anda membuat label surat karena kantor pos mengandalkan penyortiran kode pos.

Tetapi bagaimana jika Anda perlu melakukan pemeriksaan door-to-door untuk memeriksa tetangga atau untuk memberi tahu orang-orang tentang pemungutan suara yang akan datang?

Jika Anda menarik jenis daftar ini ke Excel dan mengurutkan di Street, Anda mendapatkan daftar yang diurutkan secara numerik. Seperti yang Anda lihat pada contoh di bawah ini, rekaman Drake Ave tidak bersama-sama.

Idealnya, Anda ingin mengurutkan daftar sehingga entri Drake Ave. digabungkan. Ada beberapa cara untuk melakukan ini di Excel, tetapi salah satu caranya adalah dengan membuat dua kolom dari bidang Jalan.

Kolom pertama mencerminkan substring nomor jalan dan yang kedua adalah substring nama jalan. Anda kemudian dapat menggunakan daftar tersebut berdasarkan nama jalan dan nomor jalan.

Cara Mengekstrak Nomor Jalan Menggunakan LEFT

  1. Impor data Anda ke Microsoft Excel atau gunakan contoh spreadsheet di bagian bawah artikel ini.
  2. Di cell D1, ketik Nbr.
  3. Di cell E1, ketikkan Nama Jalan.
  4. Di cell D2, ketikkan rumus Excel berikut ini =LEFT(C2,(FIND(” “,C2,1)-1))
  5. Tekan Enter. Nilai 1001 harus ditampilkan di D2
    >> Di Langkah 4, rumus Excel kita melihat nilai jalan di cell C2 (1001 Drake Ave.) dan mengekstrak konten, tetapi tidak termasuk spasi pertama. Fungsi FIND digunakan untuk mencari ruang kosong.
    >> Bagian selanjutnya melibatkan penyalinan rumus ke sisa entri. Namun, kita perlu mereferensikan cell jalan yang benar dan tidak menggunakan C2 untuk baris yang tersisa.
  6. Klik sel D2 untuk memilih rentang awal.
  7. Gerakkan mouse Anda ke pojok kanan bawah.
  8. Klik dua kali + kursor di kanan bawah. Ini akan menyalin rumus ke bawah kolom.

Di kolom D, Anda akan melihat nomor jalan yang diekstraksi.

Cara Mengekstrak Nama Jalan menggunakan RIGHT

Sekarang kita akan membuat rumus serupa untuk menangkap alamat jalan. Kali ini, kita akan mengambil konten di sebelah kanan spasi pertama dari kolom Jalan.

  1. Di cell E2, ketikkan rumus berikut =RIGHT(C2,LEN(C2)-FIND(” “,C2,1))
  2. Tekan Enter. E2 harus ditampilkan sebagai Alturas Dr.
  3. Klik cell E2 untuk memilih rentang awal.
  4. Gerakkan mouse ke pojok kanan bawah.
  5. Klik dua kali + kursor di kanan bawah. Ini akan menyalin rumus ke bawah kolom.

Kolom D dan E harus berisi konten yang diurai dari alamat jalan asli.

Spreadsheet akan terlihat seperti di bawah ini.

Membersihkan Spreadsheet dan Mengubah Format Cell

Spreadsheet sekarang memiliki bidang terpisah, tetapi Anda harus membersihkan rumusnya. Saran saya adalah mengubah rumus LEFT dan RIGHT ke nilai masing-masing.

Setelah Anda mengonversi kolom Nbr, Anda mungkin ingin mengubah tipe format menjadi angka.

  1. Klik kolom D.
  2. Klik kanan dan pilih Format cells…
  3. Pada dialog Format Cell, pilih Number.
  4. Setel Decimal places ke 0.
  5. Klik OK.

Meskipun contoh kami mengurai satu sel yang berisi informasi jalan, Anda dapat menggunakan langkah-langkah yang sama untuk mengurai tipe entri lainnya. Misalnya, Langkah 1 di atas benar-benar mengurai semuanya kecuali kata pertama karena mencari spasi kosong. Anda dapat mengubah rumus untuk menemukan nilai yang berbeda seperti koma.

Download Lembar Contoh Excel

You might also like

Leave A Reply

Your email address will not be published.