Cara Mengekstrak Angka atau Teks Dari Excel

Microsoft Excel hebat dalam bekerja dengan angka dan teks tetapi jika Anda menggunakan keduanya di sel yang sama, Anda mungkin mengalami beberapa kesulitan. Untungnya, Anda dapat mengekstraksi angka atau teks dari sel untuk bekerja dengan data Anda lebih efisien. Kami mendemonstrasikan beberapa opsi, tergantung pada format data Anda saat ini.

Nomor Excel Diformat Sebagai Teks

Ini adalah situasi yang umum, dan untungnya sangat mudah dihadapi. Kadang-kadang, sel yang hanya berisi angka diberi label yang salah atau diformat sebagai teks, mencegah Microsoft Excel menggunakannya dalam operasi.

Anda dapat melihat pada gambar di bawah ini bahwa sel-sel di kolom A diformat sebagai teks, seperti yang ditunjukkan oleh kotak format angka. Anda mungkin juga melihat bendera hijau di sudut kiri atas setiap sel.

Konversi Teks ke Angka di Excel

Jika Anda melihat bendera hijau di sudut kiri atas, pilih satu atau beberapa sel, klik tanda peringatan, dan pilih Convert to Number.

Jika tidak, pilih sel dan, di menu Format Angka di Ribbon, pilih opsi Number default.

Jika Anda membutuhkan lebih banyak opsi granular, klik kanan sel yang disorot dan pilih Format Cells, yang akan membuka menu masing-masing. Di sini, Anda dapat menyesuaikan format angka dan menambah atau menghapus desimal, menambah pemisah 1.000, atau mengelola angka negatif.

Jelas, Anda juga dapat menggunakan opsi Pita atau Format Sel yang diuraikan di atas untuk mengonversi angka menjadi teks, atau teks menjadi mata uang, waktu, atau format lain apa pun yang Anda inginkan.

Terapkan Pemformatan Angka Dengan Excel Paste Special

Agar metode ini berfungsi, Anda harus memasukkan nomor (nomor apa saja) ke dalam sel; penting bahwa sel ini juga diformat sebagai angka. Salin sel itu. Sekarang, pilih semua sel yang ingin Anda konversi ke format angka, buka Home > Paste > Paste Special, pilih Formats untuk menempelkan hanya format sel yang Anda salin pada awalnya, lalu klik OK.

Operasi ini menerapkan format sel yang Anda salin ke semua sel yang dipilih, bahkan sel teks.

Ekstrak Angka atau Teks Dari Sel Format Campuran

Sekarang kita sampai pada bagian yang sulit: mengeluarkan angka dari sel yang berisi banyak format input. Jika Anda memiliki nomor dan unit (seperti "7 sekop," seperti yang kami miliki di bawah), Anda akan mengalami masalah ini. Untuk mengatasinya, kami akan melihat beberapa cara berbeda untuk membagi sel menjadi angka dan teks, memungkinkan Anda bekerja dengan masing-masing secara individual.

Pisahkan Angka Dari Teks

Jika Anda memiliki banyak sel yang berisi campuran angka dan teks atau kelipatan keduanya, memisahkannya secara manual mungkin membutuhkan banyak waktu. Untuk melewati proses lebih cepat, Anda dapat menggunakan fungsi Teks ke Kolom Microsoft Excel.

Pilih sel yang ingin Anda konversi, buka Data > Text to Columns, dan gunakan panduan untuk memastikan sel keluar dengan benar. Untuk sebagian besar, Anda hanya perlu mengklik Next dan Finish, tetapi pastikan Anda memilih pembatas yang cocok; dalam contoh ini, koma.

Jika Anda hanya memiliki nomor satu dan dua digit, opsi Fixed Width juga dapat berguna, karena hanya akan membagi dua atau tiga karakter pertama sel. Anda bahkan dapat membuat sejumlah pemisahan dengan cara itu.

Catatan: Sel yang diformat sebagai teks tidak akan secara otomatis muncul dengan pemformatan angka (atau sebaliknya), artinya Anda mungkin masih harus mengonversi sel-sel ini seperti dijelaskan di atas.

Ekstrak Angka Atau Teks Dari String Yang Dibatasi

Metode ini agak rumit, tetapi bekerja dengan sangat baik pada dataset kecil. Apa yang kami asumsikan di sini adalah spasi memisahkan angka dan teks, meskipun metode ini juga berfungsi untuk pembatas lainnya.

Fungsi utama yang akan kita gunakan di sini adalah LEFT, yang mengembalikan karakter paling kiri dari sel. Seperti yang dapat Anda lihat dalam dataset kami di atas, kami memiliki sel dengan nomor satu, dua, dan tiga karakter, jadi kami harus mengembalikan satu, dua, atau tiga karakter paling kiri dari sel. Dengan menggabungkan LEFT dengan SEARCH function, kita dapat mengembalikan semuanya ke kiri ruang. Inilah fungsinya:

=LEFT(A1, SEARCH(" ", A1, 1))

Ini akan mengembalikan semuanya ke kiri ruang. Menggunakan gagang isian untuk menerapkan rumus ke seluruh sel, inilah yang kami dapatkan (Anda dapat melihat rumus di bilah fungsi di bagian atas gambar):

Baca Juga

Seperti yang Anda lihat, kami sekarang memiliki semua angka yang terisolasi, sehingga kami dapat memanipulasi mereka. Ingin mengisolasi teks juga? Kita dapat menggunakan fungsi RIGHT dengan cara yang sama:

=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))

Ini mengembalikan karakter X dari sisi kanan sel, di mana x adalah total panjang sel dikurangi jumlah karakter di sebelah kiri ruang.

Sekarang Anda juga dapat memanipulasi teks. Ingin menggabungkannya lagi? Cukup gunakan fungsi CONCATENATE dengan semua sel sebagai input:

=CONCATENATE(E1, F1)

Jelas, metode ini bekerja paling baik jika Anda hanya memiliki angka dan satuan, dan tidak ada yang lain. Jika Anda memiliki format sel lain, Anda mungkin harus berkreasi dengan rumus agar semuanya berfungsi dengan benar. Jika Anda memiliki dataset raksasa, akan sepadan dengan waktu yang diperlukan untuk mendapatkan rumus yang diketahui!

Ekstrak Nomor Dari Satu Ujung String Berkelanjutan

Sekarang bagaimana jika tidak ada pembatas yang memisahkan nomor dan teks Anda?

Jika Anda mengekstraksi angka dari kiri atau kanan string, Anda dapat menggunakan variasi rumus KIRI atau KANAN yang dibahas di atas:

=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))

=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))

Ini akan mengembalikan semua angka dari kiri atau kanan string.

Jika Anda mengekstraksi angka dari kanan string, Anda juga dapat menggunakan proses dua langkah. Pertama, tentukan lokasi digit pertama Anda dalam string menggunakan fungsi MIN. Kemudian, Anda dapat memasukkan informasi itu ke dalam variasi rumus KANAN, untuk memisahkan angka dari teks Anda.

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

=RIGHT(A1, LEN(A1)-B1+1)

Catatan: Saat Anda menggunakan rumus ini, ingatlah bahwa Anda mungkin harus menyesuaikan karakter kolom dan nomor sel.

Ekstrak Bilangan Dari Kedua Ujung String Berkelanjutan

Dengan strategi di atas, Anda harus dapat mengekstraksi angka atau teks dari sebagian besar sel format campuran yang membuat Anda kesulitan. Meskipun tidak, Anda mungkin dapat menggabungkannya dengan beberapa fungsi teks yang kuat yang disertakan dalam Microsoft Excel untuk mendapatkan karakter yang Anda cari. Namun, ada beberapa situasi yang jauh lebih rumit yang membutuhkan solusi yang lebih rumit.

Misalnya, saya menemukan posting di forum di mana seseorang ingin mengekstraksi angka-angka dari string seperti "45t * & 65 /", sehingga ia akan berakhir dengan "4565." Poster lain memberikan rumus berikut sebagai cara untuk melakukannya:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
 ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Sejujurnya, saya tidak tahu cara kerjanya. Tetapi menurut posting forum, itu akan mengeluarkan angka dari serangkaian angka dan karakter lain yang rumit. Intinya adalah, dengan cukup waktu, kesabaran, dan usaha, Anda dapat mengekstraksi angka dan teks dari apa saja!

Subscribe to receive free email updates: