Formula umum
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Ringkasan
Untuk menerjemahkan huruf dalam string menjadi angka, Anda bisa menggunakan rumus array berdasarkan fungsi TEXTJOIN dan VLOOKUP, dengan tabel terjemahan yang ditentukan untuk menyediakan pencarian yang diperlukan. Pada contoh yang ditunjukkan, rumus di C5 adalah:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
di mana "xtable" adalah rentang bernama E5: F10.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Pada intinya, rumus ini menggunakan operasi larik untuk menghasilkan larik huruf dari teks masukan, menerjemahkan setiap huruf secara individual menjadi angka, lalu menggabungkan semua angka bersama lagi dan mengembalikan keluaran sebagai string.
Untuk mengurai string input menjadi array atau huruf, kami menggunakan fungsi MID, ROW, LEN dan INDIRECT seperti ini:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN mengembalikan panjang teks masukan, yang digabungkan menjadi "1:" dan diserahkan ke INDIRECT sebagai teks. INDIRECT mengevaluasi teks sebagai referensi baris, dan fungsi ROW mengembalikan array angka ke MID:
MID(B5,(1;2;3),1)
MID kemudian mengekstrak satu karakter untuk setiap posisi awal dan kami memiliki:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Pada dasarnya, kami meminta VLOOKUP untuk menemukan kecocokan untuk "a", "b", dan "c" pada saat yang bersamaan. Untuk alasan yang tidak jelas, kita perlu "mendereferensi" larik ini dengan cara tertentu menggunakan fungsi T dan IF. Setelah VLOOKUP berjalan, kami memiliki:
=TEXTJOIN("",1,(9;4;6))
dan TEXTJOIN mengembalikan string "946".
Menghasilkan angka
Untuk mengeluarkan angka sebagai hasil akhir (bukan string), tambahkan nol. Operasi matematika akan memaksa string menjadi angka.
Jumlahkan angka
Untuk menjumlahkan angka bersama-sama alih-alih mencantumkannya, Anda dapat mengganti TEXTJOIN dengan SUM seperti ini:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Catatan: fungsi TEXTJOIN diperkenalkan melalui program langganan Office 365 pada tahun 2018.
