Rumus Excel: Ubah huruf menjadi angka
Terkadang, Anda ingin mengonversi a ke 1, b ke 2, c ke 3 dan seterusnya di lembar Excel. Namun, mengubahnya satu per satu hanya membuang-buang waktu. Dalam tutorial ini, saya memperkenalkan beberapa rumus Excel untuk mengatasi masalah ini.
Ubah satu huruf menjadi angka di setiap sel
Untuk mengonversi satu huruf menjadi angka di setiap sel Excel, Anda bisa menggunakan rumus di bawah ini.
Rumus umum:
| COLUMN(INDIRECT(cell_reference&1)) |
kasus
| Cell_reference: the cell that contains the letter you want to convert to number. |
Bagaimana rumus ini bekerja
Ambil contoh untuk menjelaskan rumusnya. Di sini Anda akan mengubah huruf di sel C3 menjadi angka. Silakan gunakan rumus ini.
| =COLUMN(INDIRECT(C3&1)) |
Tekan Enter kunci.
Penjelasan
INDIRECT berfungsi: fungsi ini digunakan untuk mengubah string teks menjadi referensi yang valid. Di sini TIDAK LANGSUNG (C3 & 1) Anda dapat melihatnya sebagai berikut:
TIDAK LANGSUNG (C3 & 1)
= TIDAK LANGSUNG (s & 1)
= TIDAK LANGSUNG (s1)
COLUMN fungsi: Yang COLUMN fungsi mengembalikan jumlah kolom dari referensi yang diberikan. Sekarang rumusnya adalah COLUMN (s1) yang mengembalikan 19.
Keterangan:
1. Rumus ini mengembalikan jumlah kolom yang berarti jika ada lebih dari satu huruf dalam sel, maka akan dikembalikan seperti contoh di bawah ini.
2. Rumusnya case insensitive.
Ubah beberapa huruf menjadi string angka di setiap sel
Jika Anda ingin mengubah huruf di setiap sel menjadi angka seperti gambar di bawah ini, Anda memerlukan rumus array yang kompleks.
Rumus umum:
| TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(cell_refer,ROW(INDIRECT("1:"&LEN(cell_refer))),1))),Rtable,2,0)) |
kasus
| Cell_refer: the cell that you want to convert the letters to numbers. |
| Rtable:a table with two columns, one lists all letters, one lists the relative numbers. |
Bagaimana rumus ini bekerja
Misalkan Anda ingin mengubah huruf dalam rentang B3: B8 menjadi angka, lakukan seperti ini:
Pertama, buat tabel untuk mencantumkan semua huruf dan nomor relatifnya.
Kemudian pilih rentang tabel dan pergi ke Kotak nama (di samping bilah Formula) untuk memberinya nama jarak, di sini nama rentangnya adalah Rtabel. lihat tangkapan layar:
Sekarang gunakan rumus ini:
| =TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)) |
Tekan Enter kunci, semua huruf pertama dari setiap kata di sel A1 diekstraksi.
Penjelasan
BARIS (TIDAK LANGSUNG ("1:" & LEN (B3))): Yang Fungsi ROW mengembalikan jumlah baris, ada rumus array, lalu mengembalikan {1; 2; 3}. MID (B3, ROW (TIDAK LANGSUNG ("1:" & LEN (B3))), 1): Yang Fungsi MID mengembalikan karakter di posisi tertentu dari string yang diberikan. Rumus tersebut dapat dilihat seperti di bawah ini
=MID(B3,{1;2;3},1)
= MID ("acd", {1; 2; 3}, 1)
= {"a"; "c"; "d"}
VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0): Yang Fungsi VLOOKUP digunakan untuk mencari bilangan yang cocok dari "a", "c", "d" di kolom kedua rentang R tabel. Kemudian dia kembali {1; 3; 4}.
TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)): Yang Fungsi TEXTJOIN adalah fungsi baru di Excel 2019 dan 365, ini digunakan untuk menggabungkan semua teks dengan pembatas. Ini dia kembali 134.
Keterangan:
Rumus ini tidak membedakan huruf besar / kecil.
Catatan:
Misalkan a = 1, b = 2, c = 3, d = 4, Anda dapat menggunakan rumus sebagai berikut:
| =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"a",1),"b",2),"c",3),"d",4) |
Tekan Enter kunci.
Rumus ini peka huruf besar / kecil.
File Contoh
Klik untuk mendownload file contoh
Rumus Relatif
- Hapus Karakter N Pertama Dari Sel
Di sini memperkenalkan rumus untuk menghapus karakter dari sisi kiri string teks di Excel. - Hapus Dari Kanan Teks
Di sini memperkenalkan rumus untuk menghapus karakter dari sisi kanan string teks di Excel. - Ekstrak Kata Terakhir Dari String Teks Di Excel
Tutorial ini memberikan rumus untuk mengekstrak kata terakhir dari string teks give. - Ekstrak Kata Pertama Dari String Teks Di Excel
Tutorial ini memberikan rumus untuk mengekstrak kata pertama dari string teks give.
Fungsi Relatif
- Fungsi INDIRECT
Ubah string teks menjadi referensi yang valid. - Fungsi COLUMN
Kembalikan jumlah kolom yang rumusnya muncul atau nomor kolom referensi yang diberikan. - Fungsi ROW
Kembalikan nomor baris dari sebuah referensi. - MID
Kembalikan karakter tertentu dari tengah string teks.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
