Công thức đọc số thành chữ Tiếng Anh không dùng Addin

 Bài viết chia sẻ công thức đọc số thành chữ Tiếng Anh hoàn toàn bằng công thức Excel thuần túy mà không cần Add-in

Videos hướng dẫn cách copy công thức đọc số thành chữ Tiếng Anh vào bảng tính Excel

Công thức đọc số thành chữ tiếng anh cho Excel 365:

=LET(so,INT(G2),le,LAMBDA(a,CHOOSE(a+1,"","one","two","three","four","five","six","seven","eight","nine")),ty,LAMBDA(b,CHOOSE(b,"twen","thir","forty","fif","six","seven","eigh","nine")),

te,LAMBDA(c,CHOOSE(c,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen")),

doc,LAMBDA(y,IF(INT(y/100)=0,"",le(INT(y/100)) & " hundred ") & IF(INT(y/10)-INT(y/100)*10>1,ty(INT(y/10)-INT(y/100)*10-1) & "ty" & IF(y-INT(y/10)*10=0,"","-" & le(y-INT(y/10)*10)),IF(INT(y/10)-INT(y/100)*10=1,te(y-INT(y/10)*10+1),le(y-INT(y/10)*10)))),

bl,IF(INT(so/10^9)=0,"",doc(INT(so/10^9)) & " billion "),

ml,IF(INT(so/10^6)-INT(so/10^9)*1000=0,"",doc(INT(so/10^6)-INT(so/10^9)*1000) & " million "),

th,IF(INT(so/10^3)-INT(so/10^6)*1000=0,"",doc(INT(so/10^3)-INT(so/10^6)*1000) & " thousand "),

hr,IF(so-INT(so/10^3)*1000=0,"",doc(so-INT(so/10^3)*1000)),

jn,TRIM(bl&ml&th&hr),IF(so=0,"Zero",UPPER(LEFT(jn)) & LOWER(MID(jn,2,222)) & " Vietnamese dong."))

Công thức đọc số thành chữ tiếng anh cho Excel bản cũ:

=IF(G2=0, "zero",IF(INT(G2/10^9)-INT(G2/10^12)*1000=0,"",IF(INT(G2/10^11)-INT(G2/10^12)*10=0,"",CHOOSE(INT(G2/10^11)-INT(G2/10^12)*10+1,"","one","two","three","four","five","six","seven","eight","nine") & " hundred ") & IF(INT(G2/10^10)-INT(G2/10^11)*10>1,CHOOSE(INT(G2/10^10)-INT(G2/10^11)*10-1,"twen","thir","forty","fif","six","seven","eigh","nine") & "ty" & IF(INT(G2/10^9)-INT(G2/10^10)*10=0,"","-" & CHOOSE(INT(G2/10^9)-INT(G2/10^10)*10+1,"","one","two","three","four","five","six","seven","eight","nine")),IF(INT(G2/10^10)-INT(G2/10^11)*10=1,CHOOSE(INT(G2/10^9)-INT(G2/10^10)*10+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),CHOOSE(INT(G2/10^9)-INT(G2/10^10)*10+1,"","one","two","three","four","five","six","seven","eight","nine"))) & " billion ")

& IF(INT(G2/10^6)-INT(G2/10^9)*1000=0,"",IF(INT(G2/10^8)-INT(G2/10^9)*10=0,"",CHOOSE(INT(G2/10^8)-INT(G2/10^9)*10+1,"","one","two","three","four","five","six","seven","eight","nine") & " hundred ") & IF(INT(G2/10^7)-INT(G2/10^8)*10>1,CHOOSE(INT(G2/10^7)-INT(G2/10^8)*10-1,"twen","thir","forty","fif","six","seven","eigh","nine") & "ty" & IF(INT(G2/10^6)-INT(G2/10^7)*10=0,"","-" & CHOOSE(INT(G2/10^6)-INT(G2/10^7)*10+1,"","one","two","three","four","five","six","seven","eight","nine")),IF(INT(G2/10^7)-INT(G2/10^8)*10=1,CHOOSE(INT(G2/10^6)-INT(G2/10^7)*10+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),CHOOSE(INT(G2/10^6)-INT(G2/10^7)*10+1,"","one","two","three","four","five","six","seven","eight","nine"))) & " million ")

& IF(INT(G2/10^3)-INT(G2/10^6)*1000=0,"",IF(INT(G2/10^5)-INT(G2/10^6)*10=0,"",CHOOSE(INT(G2/10^5)-INT(G2/10^6)*10+1,"","one","two","three","four","five","six","seven","eight","nine") & " hundred ") & IF(INT(G2/10^4)-INT(G2/10^5)*10>1,CHOOSE(INT(G2/10^4)-INT(G2/10^5)*10-1,"twen","thir","forty","fif","six","seven","eigh","nine") & "ty" & IF(INT(G2/10^3)-INT(G2/10^4)*10=0,"","-" & CHOOSE(INT(G2/10^3)-INT(G2/10^4)*10+1,"","one","two","three","four","five","six","seven","eight","nine")),IF(INT(G2/10^4)-INT(G2/10^5)*10=1,CHOOSE(INT(G2/10^3)-INT(G2/10^4)*10+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),CHOOSE(INT(G2/10^3)-INT(G2/10^4)*10+1,"","one","two","three","four","five","six","seven","eight","nine"))) & " thousand ")

& IF(G2-INT(G2/10^3)*1000=0,"",IF(INT(G2/10^2)-INT(G2/10^3)*10=0,"",CHOOSE(INT(G2/10^2)-INT(G2/10^3)*10,"one","two","three","four","five","six","seven","eight","nine") & " hundred ") & IF(INT(G2/10^1)-INT(G2/10^2)*10>1,CHOOSE(INT(G2/10^1)-INT(G2/10^2)*10-1,"twen","thir","forty","fif","six","seven","eigh","nine") & "ty" & IF(INT(G2)-INT(G2/10^1)*10=0,"","-" & CHOOSE(INT(G2)-INT(G2/10^1)*10+1,"","one","two","three","four","five","six","seven","eight","nine")),IF(INT(G2/10^1)-INT(G2/10^2)*10=1,CHOOSE(INT(G2)-INT(G2/10)*10+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),CHOOSE(INT(G2)-INT(G2/10)*10+1,"","one","two","three","four","five","six","seven","eight","nine"))))) & " Vietnamese dong."

Cách sử dụng hàm đọc số thành chữ tiếng anh:

- Thay thế tọa độ ô G2 trong công thức

- Dùng Ctrl+H để thay thế hàng loạt, tránh sai xót (với công thức dành cho Office phiên bản thường)

ĐÀO TẠO EXCEL CHO NGƯỜI ĐI LÀM VÀ DOANH NGHIỆP

{ĐT+ZALO} 038 696 1334