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 mengekstrak angka atau teks dari sel untuk bekerja dengan data Anda secara lebih efisien. Kami mendemonstrasikan beberapa opsi, bergantung pada format data Anda saat ini.

Nomor Excel Diformat sebagai Teks

Ini adalah situasi yang umum, dan---untungnya---sangat mudah untuk ditangani. Terkadang, sel yang hanya berisi angka diberi label atau format yang salah sebagai teks, sehingga 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 lebih sel, klik tanda peringatan, dan pilih Konversikan ke Angka .

Jika tidak, pilih sel dan, di menu Format Angka di Pita, pilih default Nomor pilihan.

Jika Anda membutuhkan lebih banyak opsi granular, klik kanan sel yang disorot dan pilih Format Sel , yang akan membuka menu masing-masing. Di sini, Anda dapat menyesuaikan format angka dan menambahkan atau menghapus desimal, menambahkan 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 ke mata uang, waktu, atau format lain yang Anda inginkan.

Terapkan Pemformatan Angka Dengan Tempel Khusus Excel

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 Beranda > Tempel > Tempel Spesial , Pilih Format untuk hanya menempelkan format sel yang Anda salin pada awalnya, lalu klik oke .

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 ke bagian yang sulit: mengeluarkan angka dari sel yang berisi berbagai format input. Jika Anda memiliki angka dan satuan (seperti '7 sekop,' seperti yang kami miliki di bawah), Anda akan mengalami masalah ini. Untuk mengatasinya, kita 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 waktu yang lama. Untuk melewati proses lebih cepat, Anda dapat menggunakan Microsoft Excel Teks ke Kolom fungsi.

Pilih sel yang ingin Anda konversi, buka Data > Teks ke Kolom , dan gunakan wizard untuk memastikan sel keluar dengan benar. Untuk sebagian besar, Anda hanya perlu mengklik Lanjut dan Menyelesaikan , tetapi pastikan Anda memilih pembatas yang cocok; dalam contoh ini, koma.

Jika Anda hanya memiliki satu dan dua digit angka, Lebar Tetap opsi dapat berguna juga, karena hanya akan memisahkan dua atau tiga karakter pertama sel. Anda bahkan dapat membuat sejumlah split dengan cara itu.

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

Ekstrak Angka atau Teks Dari Delimited String

Metode ini agak rumit, tetapi bekerja sangat baik pada kumpulan data kecil. Apa yang kami asumsikan di sini adalah bahwa spasi memisahkan nomor 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 sebuah sel. Seperti yang Anda lihat di dataset kami di atas, kami memiliki sel dengan angka satu, dua, dan tiga karakter, jadi kami harus mengembalikan satu, dua, atau tiga karakter paling kiri dari sel. Dengan menggabungkan KIRI dengan fungsi PENCARIAN , kita dapat mengembalikan semuanya ke kiri ruang. Berikut fungsinya:

=KIRI(A1, SEARCH(' ', A1, 1))

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

Seperti yang Anda lihat, kami sekarang memiliki semua nomor yang terisolasi, sehingga kami dapat memanipulasinya. Ingin mengisolasi teks juga? Kita dapat menggunakan fungsi KANAN dengan cara yang sama:

=KANAN(A1, LEN(A1)-SEARCH(' ', A1, 1))

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

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 unit, 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 kumpulan data raksasa, itu akan sepadan dengan waktu yang diperlukan untuk mendapatkan rumusnya!

Ekstrak Nomor Dari Satu Ujung String Berkelanjutan

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

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

= KIRI (A1, SUM (LEN (A1) -LEN (PENGGANTI (A1, {'0', '1', '2', '3', '4', '5', '6', '7' , '8', '9'}, ''))))

= KANAN (A1, SUM (LEN (A1) -LEN (GANTI (A1, {'0', '1', '2', '3', '4', '5', '6', '7' , '8', '9'}, ''))))

Ini akan mengembalikan semua angka dari kiri atau kanan string.

Jika kamu mengekstraksi nomor 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 nomor Anda dari teks Anda.

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

cara mencari dpi gambar

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

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

Ekstrak Angka Dari Kedua Ujung String Berkelanjutan

Dengan strategi di atas, Anda harus dapat mengekstrak angka atau teks dari sebagian besar sel format campuran yang memberi Anda masalah. Bahkan jika tidak, Anda mungkin dapat menggabungkannya dengan beberapa fungsi teks canggih 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 forum di mana seseorang ingin mengekstrak angka dari string seperti '45t*&65/', sehingga dia akan berakhir dengan '4565.' Poster lain memberikan rumus berikut sebagai salah satu cara untuk melakukannya:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(:),1))*

ROW(:),0),ROW(:))+1,1)*10^ROW(:)/10)

Sejujurnya, saya tidak tahu cara kerjanya. Tetapi menurut posting forum, itu akan mengeluarkan angka dari rangkaian angka dan karakter lain yang rumit. Intinya adalah, dengan waktu, kesabaran, dan usaha yang cukup, Anda dapat mengekstrak angka dan teks dari apa saja! Anda hanya perlu menemukan sumber daya yang tepat.

Setelah beberapa tips Excel lagi? ini cara menyalin rumus di excel .

Membagikan Membagikan Menciak Surel Cara Membersihkan PC Windows Anda Menggunakan Command Prompt

Jika PC Windows Anda kehabisan ruang penyimpanan, bersihkan sampah menggunakan utilitas Command Prompt yang cepat ini.

Baca Selanjutnya Topik-topik yang berkaitan

Produktifitas
Spreadsheet
Microsoft Excel

Tentang Penulis Tina Sieber(831 Artikel Diterbitkan)

Saat menyelesaikan PhD, Tina mulai menulis tentang teknologi konsumen pada tahun 2006 dan tidak pernah berhenti. Sekarang juga seorang editor dan SEO, Anda dapat menemukannya di Indonesia atau mendaki jalur terdekat.

Berlangganan newsletter kami

Bergabunglah dengan buletin kami untuk kiat teknologi, ulasan, ebook gratis, dan penawaran eksklusif!

Klik di sini untuk berlangganan