Khi tạo email, tên đăng nhập hay mã khách hàng từ họ tên, bạn thường phải đổi "Nguyễn Thị Ánh" thành "Nguyen Thi Anh". Excel không có hàm có sẵn để bỏ dấu tiếng Việt trong Excel, nên bài viết này hướng dẫn 3 cách: tự tạo hàm bằng VBA, dùng công thức không cần macro trên Microsoft 365, Excel 2024 hoặc Excel bản web, và dùng Unikey Toolkit.

Cách nhanh nhất là chép hàm BoDau ở phần dưới vào một Module VBA, rồi gõ =BoDau(A2) vào ô B2. Với ô A2 là "Nguyễn Thị Ánh", ô B2 trả về "Nguyen Thi Anh". Nếu không muốn dùng macro và đang dùng Microsoft 365, Excel 2024 hoặc Excel bản web, bạn dùng công thức REDUCE kết hợp SUBSTITUTE với một bảng ký tự đặt ở vùng riêng. Sau khi bỏ dấu, bạn gõ =LOWER(SUBSTITUTE(B2," ",""))&"@congty.vn" vào ô C2 để có email nguyenthianh@congty.vn.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy.

Bạn nên chọn cách bỏ dấu nào?

Cách

Điều kiện

Kết quả có tự cập nhật không

Cách này hợp khi

Hàm BoDau bằng VBA

Mọi bản Excel trên Windows, file lưu dạng .xlsm

Kết quả tự tính lại khi họ tên thay đổi.

Bạn cần dùng lâu dài và không ngại file có macro.

Công thức REDUCE và SUBSTITUTE

Microsoft 365, Excel 2024 hoặc Excel bản web

Kết quả tự tính lại khi họ tên thay đổi.

Công ty chặn macro, hoặc file phải giữ dạng .xlsx.

Unikey Toolkit

Máy có cài Unikey

Kết quả là chữ cố định, không tự cập nhật.

Bạn chỉ cần bỏ dấu một lần cho một danh sách.

Bạn tạo hàm BoDau bằng VBA như thế nào?

Bảng ví dụ ở sheet Danh sách có 6 họ tên ở vùng A2:A7. Cột B sẽ chứa họ tên không dấu, và cột C sẽ chứa email.

Bước 1: Bạn mở trình soạn VBA và thêm Module

Bạn nhấn Alt + F11 để mở trình soạn VBA. Trong trình soạn VBA, bạn bấm menu Insert rồi chọn Module.

Trình soạn VBA với menu Insert đang mở, con trỏ chuột chỉ vào mục Module
Mục Module tạo một trang code mới trong file đang mở

Bước 2: Bạn chép code hàm BoDau vào Module

Bạn chép toàn bộ đoạn code sau vào cửa sổ code vừa mở:

Function BoDau(ByVal s As String) As String
    Dim ma, khong As String, m, i As Long, j As Long, c As String
    ma = Split("E1 E0 1EA3 E3 1EA1 103 1EAF 1EB1 1EB3 1EB5 1EB7 E2 1EA5 1EA7 1EA9 1EAB 1EAD " & _
        "E9 E8 1EBB 1EBD 1EB9 EA 1EBF 1EC1 1EC3 1EC5 1EC7 ED EC 1EC9 129 1ECB " & _
        "F3 F2 1ECF F5 1ECD F4 1ED1 1ED3 1ED5 1ED7 1ED9 1A1 1EDB 1EDD 1EDF 1EE1 1EE3 " & _
        "FA F9 1EE7 169 1EE5 1B0 1EE9 1EEB 1EED 1EEF 1EF1 FD 1EF3 1EF7 1EF9 1EF5 111")
    khong = "aaaaaaaaaaaaaaaaaeeeeeeeeeeeiiiiiooooooooooooooooouuuuuuuuuuuyyyyyd"
    For Each m In Array(768, 769, 770, 771, 774, 777, 795, 803)
        s = Replace(s, ChrW(m), "")
    Next m
    For i = 1 To Len(s)
        c = LCase(Mid(s, i, 1))
        For j = 0 To UBound(ma)
            If c = ChrW("&H" & ma(j)) Then
                If c = Mid(s, i, 1) Then
                    Mid(s, i, 1) = Mid(khong, j + 1, 1)
                Else
                    Mid(s, i, 1) = UCase(Mid(khong, j + 1, 1))
                End If
                Exit For
            End If
        Next j
    Next i
    BoDau = s
End Function
Cửa sổ code Module1 chứa hàm BoDau từ dòng Function tới dòng End Function
Hàm BoDau nằm trong Module1 của file đang mở

Đoạn code có 4 phần:

Phần code

Ý nghĩa

ma = Split(...)

Dòng này liệt kê mã Unicode của 67 chữ thường có dấu, viết ở hệ 16. Ví dụ, E1 là chữ á, 1EA1 là chữ ạ và 111 là chữ đ.

khong = "aaa...d"

Chuỗi này chứa 67 chữ không dấu theo đúng thứ tự của danh sách mã: 17 chữ a, 11 chữ e, 5 chữ i, 17 chữ o, 11 chữ u, 5 chữ y và 1 chữ d.

Vòng For Each m

Vòng lặp này xóa 8 dấu rời mà kiểu gõ Unicode tổ hợp hay dùng, gồm dấu huyền, sắc, mũ, ngã, trăng, hỏi, móc và nặng.

Vòng For i

Vòng lặp này đổi từng ký tự sang chữ thường bằng LCase rồi tìm trong danh sách mã. Nếu ký tự gốc là chữ hoa, hàm trả về chữ hoa bằng UCase, nên chữ Đ thành D và chữ Ứ thành U.

Hàm ghi các chữ có dấu bằng mã Unicode thay vì gõ thẳng chữ, vì trình soạn VBA chỉ hiện chữ theo bảng mã mà Windows đặt ở mục Language for non-Unicode programs. Trên đa số máy, mục này không phải tiếng Việt, nên các chữ như ạ, ễ, ị, ứ, ư bị đổi thành dấu hỏi chấm khi bạn gõ hoặc dán vào trình soạn VBA, nên một hàm gõ thẳng chữ có dấu sẽ không chạy đúng. Bạn có thể thấy điều này ngay trong ảnh trên: khung Project hiện tên sheet Công thức thành "Công th?c".

Bước 3: Bạn dùng hàm BoDau trên sheet

Bạn nhấn Alt + Q để đóng trình soạn VBA và quay lại Excel. Bạn chọn ô B2, gõ công thức sau rồi nhấn Enter:

=BoDau(A2)

Bạn đang gõ vào ô B2 công thức =BoDau(A2), thanh công thức hiện cùng nội dung
Hàm BoDau được dùng như một hàm có sẵn của Excel

Bạn kéo công thức ở ô B2 xuống ô B7.

Ô B2 đang chọn, thanh công thức hiện =BoDau(A2), cột B có 6 họ tên không dấu
Hàm giữ nguyên chữ hoa đầu mỗi từ, nên Trần Đức Thắng thành Tran Duc Thang

Bước 4: Bạn tạo email từ họ tên không dấu

Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:

=LOWER(SUBSTITUTE(B2," ",""))&"@congty.vn"

Bạn đang gõ vào ô C2 công thức =LOWER(SUBSTITUTE(B2," ",""))&"@congty.vn"
SUBSTITUTE xóa dấu cách, LOWER đổi chữ hoa thành chữ thường

Hàm SUBSTITUTE thay mọi dấu cách trong ô B2 bằng chuỗi rỗng, nên "Nguyen Thi Anh" thành "NguyenThiAnh". Hàm LOWER đổi chuỗi này thành "nguyenthianh", rồi dấu & nối thêm đuôi "@congty.vn". Bạn kéo công thức xuống ô C7.

Ô C2 đang chọn, cột C có 6 email từ nguyenthianh@congty.vn tới huynhthimyduyen@congty.vn
Mỗi email được ghép từ họ tên không dấu, không có dấu cách

Nếu không cần cột họ tên không dấu, bạn gộp 2 bước thành một công thức ở ô C2: =LOWER(SUBSTITUTE(BoDau(A2)," ",""))&"@congty.vn". Nếu cần mã khách hàng viết hoa như NGUYENTHIANH, bạn thay LOWER bằng UPPER và bỏ phần nối đuôi email.

Bước 5: Bạn lưu file dạng .xlsm

Bạn vào File, chọn Save As, rồi chọn kiểu Excel Macro-Enabled Workbook (*.xlsm) ở ô kiểu file. Hàm BoDau chỉ dùng được trong file chứa code này. Nếu muốn làm quen thêm với trình soạn VBA và macro, bạn có thể đọc bài tạo menu trong Excel bằng VBA.

Bạn bỏ dấu không cần macro trên Excel 365 như thế nào?

Cách này dùng công thức nên file vẫn lưu được dạng .xlsx. Công thức cần Microsoft 365, Excel 2024 hoặc Excel bản web. Excel 2019 và Excel 2021 chưa có hàm REDUCE và LAMBDA nên báo lỗi #NAME?.

Bước 1: Bạn lập bảng ký tự ở vùng riêng

Bạn tạo một sheet mới tên Công thức. Ô A1 là tiêu đề Họ và tên, ô B1 là tiêu đề Không dấu, và bạn chép 6 họ tên của ví dụ vào vùng A2:A7.

Tiếp theo, bạn gõ bảng ký tự vào vùng E1:F15 của sheet này. Ô E1 và ô F1 là 2 tiêu đề Không dấu và Có dấu, còn 14 dòng chữ nằm từ dòng 2 tới dòng 15. Cột E là chữ không dấu, và cột F là mọi chữ có dấu tương ứng, viết liền nhau không có dấu cách:

Không dấu (cột E)

Có dấu (cột F)

a

áàảãạăắằẳẵặâấầẩẫậ

A

ÁÀẢÃẠĂẮẰẲẴẶÂẤẦẨẪẬ

e

éèẻẽẹêếềểễệ

E

ÉÈẺẼẸÊẾỀỂỄỆ

i

íìỉĩị

I

ÍÌỈĨỊ

o

óòỏõọôốồổỗộơớờởỡợ

O

ÓÒỎÕỌÔỐỒỔỖỘƠỚỜỞỠỢ

u

úùủũụưứừửữự

U

ÚÙỦŨỤƯỨỪỬỮỰ

y

ýỳỷỹỵ

Y

ÝỲỶỸỴ

d

đ

D

Đ

Bảng cần có cả dòng chữ hoa, vì hàm SUBSTITUTE phân biệt chữ hoa và chữ thường. Nếu bảng chỉ có dòng chữ thường, chữ Á trong "Ánh" sẽ không được đổi.

Bước 2: Bạn gõ công thức REDUCE

Bạn chọn ô B2, gõ công thức sau rồi nhấn Enter:

=LET(co,CONCAT($F$2:$F$15),khong,CONCAT(REPT($E$2:$E$15,LEN($F$2:$F$15))),REDUCE(A2,SEQUENCE(LEN(co)),LAMBDA(kq,i,SUBSTITUTE(kq,MID(co,i,1),MID(khong,i,1)))))

Bạn đang gõ vào ô B2 công thức LET và REDUCE, bảng ký tự nằm ở cột E và F
Công thức đang gõ tràn sang phải và che 2 dòng đầu của bảng ký tự, bạn xem đủ bảng ở ảnh kết quả bên dưới

Công thức lấy bảng ký tự ở vùng E2:F15, nên bạn khóa vùng này bằng dấu $ để vùng không bị dời khi kéo công thức xuống. Công thức này có 3 phần:

  • Biến co nối cột F thành một chuỗi 134 chữ có dấu, bắt đầu bằng "áàảãạ".

  • Biến khong lặp mỗi chữ ở cột E đúng bằng số chữ có dấu cùng dòng, rồi nối lại. Hàm REPT lặp chữ a 17 lần, chữ A 17 lần, và cứ thế tới chữ D. Nhờ vậy, chữ thứ i của khong luôn là bản không dấu của chữ thứ i trong co.

  • Hàm REDUCE bắt đầu từ họ tên ở ô A2 và chạy 134 lượt. Ở lượt thứ i, hàm SUBSTITUTE thay chữ thứ i của co bằng chữ thứ i của khong, rồi chuyển kết quả cho lượt sau.

Excel bản tiếng Anh có thể tự đổi chữ i đứng riêng thành chữ I khi bạn gõ, như trong ảnh, do tính năng AutoCorrect. Công thức vẫn chạy đúng, vì Excel không phân biệt chữ hoa và chữ thường trong tên biến. Bạn kéo công thức xuống ô B7.

Ô B2 đang chọn, cột B có 6 họ tên không dấu giống kết quả của hàm BoDau
Công thức cho kết quả giống hàm BoDau mà không cần macro

Nếu muốn đặt bảng ký tự ở một sheet riêng, ví dụ sheet BangKyTu, bạn đổi các vùng trong công thức thành BangKyTu!$E$2:$E$15 và BangKyTu!$F$2:$F$15. Để tạo email, bạn dùng công thức LOWER và SUBSTITUTE như ở cách 1. Nếu bạn muốn hiểu kỹ hơn các hàm SUBSTITUTE, MID và LEN, bạn có thể đọc bài hàm xử lý chuỗi trong Excel.

Bạn bỏ dấu bằng Unikey Toolkit như thế nào?

Unikey có công cụ chuyển mã kèm tùy chọn loại bỏ dấu. Cách này cho kết quả là chữ cố định, nên khi họ tên gốc thay đổi, bạn phải làm lại. Các bước như sau:

  1. Bạn quét chọn vùng họ tên trong Excel rồi nhấn Ctrl + C.

  2. Bạn nhấp chuột phải vào biểu tượng Unikey ở góc phải thanh tác vụ và chọn Công cụ... để mở Unikey Toolkit.

  3. Bạn chọn bảng mã Nguồn và bảng mã Đích cùng là bảng mã của font đang dùng. Với font Arial, Calibri hay Times New Roman, bạn chọn Unicode.

  4. Bạn đánh dấu ô Loại bỏ dấu, bấm Chuyển mã, rồi đóng công cụ.

  5. Bạn chọn ô đầu của cột kết quả, ví dụ ô B2, rồi nhấn Ctrl + V.

Bạn hay gặp lỗi gì khi bỏ dấu tiếng Việt?

Chữ gõ bằng Unicode tổ hợp vẫn còn dấu

Một số dữ liệu được gõ bằng kiểu Unicode tổ hợp, hoặc được chép từ nguồn dùng kiểu này. Ở kiểu tổ hợp, chữ "ễ" được lưu thành 2 ký tự là chữ "ê" và một dấu ngã rời. Ô A9 và ô A10 trong ví dụ dưới đây nhìn giống hệt nhau, nhưng ô A10 được gõ bằng Unicode tổ hợp. Ô B9 và ô B10 dùng cùng công thức REDUCE ở trên, chỉ đổi A2 thành A9 và A10. Công thức REDUCE ở ô B10 chỉ đổi được chữ ê thành e, nên kết quả vẫn còn dấu ngã, dấu nặng và dấu sắc.

Để kiểm tra, bạn gõ tiêu đề Số ký tự vào ô C1, gõ =LEN(A9) vào ô C9 và gõ =LEN(A10) vào ô C10.

Ô C10 đang chọn, thanh công thức hiện =LEN(A10), ô C9 là 14, ô C10 là 17, ô B10 vẫn còn dấu
Hai họ tên giống nhau nhưng hàm LEN đếm ra 14 và 17 ký tự

Hàm LEN giúp bạn phát hiện lỗi này. Họ tên "Nguyễn Thị Ánh" có 14 ký tự ở kiểu thường, nhưng có 17 ký tự ở kiểu tổ hợp vì có thêm 3 dấu rời. Bạn có 3 cách sửa:

  • Hàm BoDau ở cách 1 đã có vòng xóa dấu rời, nên hàm trả về đúng "Nguyen Thi Anh" cho cả ô A10.

  • Với công thức REDUCE, bạn thêm một bước xóa dấu rời trước khi tra bảng ký tự. Bạn gõ công thức sau vào ô B10: =LET(s,REDUCE(A10,UNICHAR({768,769,770,771,774,777,795,803}),LAMBDA(t,m,SUBSTITUTE(t,m,""))),co,CONCAT($F$2:$F$15),khong,CONCAT(REPT($E$2:$E$15,LEN($F$2:$F$15))),REDUCE(s,SEQUENCE(LEN(co)),LAMBDA(kq,i,SUBSTITUTE(kq,MID(co,i,1),MID(khong,i,1))))). Công thức này trả về "Nguyen Thi Anh".

  • Với Unikey Toolkit, bạn chọn bảng mã Nguồn là Unicode tổ hợp và bảng mã Đích là Unicode để đưa dữ liệu về kiểu thường trước khi bỏ dấu.

File .xlsx làm mất hàm BoDau

Khi bạn nhấn Ctrl + S trên một file .xlsx có chứa code VBA, Excel hiện hộp cảnh báo rằng file .xlsx không lưu được VB project, tức là phần code VBA. Ở bản Microsoft 365 mới, hộp cảnh báo có 3 nút: Save, Save and erase features và Go back. Ở Excel 2016 và Excel 2019, hộp cảnh báo có nút Yes và No, và bạn bấm No để quay lại chọn kiểu .xlsm.

Hộp cảnh báo của Excel khi lưu file .xlsx có code VBA, con trỏ chuột chỉ vào nút Go back
Bạn bấm Go back để quay lại và chọn kiểu file .xlsm

Nếu bạn bấm Save hoặc Save and erase features, file vẫn ở dạng .xlsx và code không được lưu vào file. Lần sau mở file, code đã mất, và khi Excel tính lại, ví dụ lúc bạn sửa họ tên hoặc nhấn Ctrl + Alt + F9, các ô dùng hàm BoDau báo lỗi #NAME?. Bạn cần bấm Go back, rồi vào File, chọn Save As và chọn kiểu Excel Macro-Enabled Workbook (*.xlsm).

Bạn có thể gặp thêm các lỗi sau

Lỗi

Nguyên nhân

Cách sửa

Kết quả còn chữ Đ hoặc đ, ví dụ "Tran Đuc Thang"

Bảng ký tự thiếu 2 dòng d và D, hoặc công thức chỉ lấy tới dòng 13.

Bạn thêm dòng d với chữ đ và dòng D với chữ Đ, rồi kiểm tra vùng trong công thức kéo tới dòng 15.

Kết quả có chữ thừa, ví dụ "NguyenaThiaAnh"

Ô có dấu ở cột F chứa dấu cách giữa các chữ, nên công thức thay mọi dấu cách bằng chữ a.

Bạn xóa hết dấu cách trong các ô cột F.

Công thức REDUCE báo #NAME?

Excel của bạn là Excel 2019 hoặc Excel 2021, chưa có hàm REDUCE và LAMBDA. Hai hàm này chỉ có trên Microsoft 365, Excel 2024 và Excel bản web.

Bạn dùng hàm BoDau ở cách 1 hoặc Unikey Toolkit.

Hàm BoDau báo #NAME? ở file khác

Code chỉ nằm trong file bạn đã chép vào.

Bạn chép code vào Module của file đang cần dùng và lưu file dạng .xlsm.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.