Trong công việc quản trị dữ liệu, báo cáo kinh doanh, hay đơn giản là khi nhập liệu vào Excel, chúng ta thường gặp phải tình huống cần loại bỏ dấu tiếng Việt để làm sạch dữ liệu, chuẩn hoá tên, địa chỉ hoặc các trường nhập liệu khác. Việc chuyển từ ký tự có dấu sang không dấu không chỉ giúp tránh lỗi khi so sánh chuỗi, mà còn tăng tính nhất quán khi kết nối dữ liệu với các hệ thống không hỗ trợ Unicode.
Bài viết này sẽ trình bày chi tiết các phương pháp chuyển ký tự có dấu sang không dấu trong Excel, từ công thức đơn giản nhất, dùng hàm “SUBSTITUTE” và “TRIM”, đến cách viết hàm tùy chỉnh bằng VBA, Power Query, và thậm chí là một số mẹo thủ công. Bạn sẽ được hướng dẫn từng bước, có ví dụ thực tế, và lưu ý những vấn đề thường gặp khi làm việc với tiếng Việtμών trong Excel.
Có thể bạn quan tâm: Cách Chia Một Cột Thành Hai Cột Trong Excel
1. Tại sao cần phải chuyển ký tự có dấu sang không dấu trong Excel?
Chuẩn hoá dữ liệu: Khi nhập dữ liệu từ nhiều nguồn khác nhau, ký tự có dấu thường_compare với ký tự không dấu khác nhau, gây ra lỗi khi lọc, sắp xếp hay nhóm dữ liệu. Việc chuẩn hoá bằng cách loại bỏ dấu giúp dữ liệu trở nên đồng nhất.
Tìm kiếm và lọc: Excel có thể không tìm thấy dữ liệu nếu người dùng nhập “Đặng” và dữ liệu lưu trữ là “Dang”. Khi chuyển thành không dấu, tìm kiếm trở nên linh hoạt hơn.
Kết nối với hệ thống khác: Nhiều hệ thống CRM, ERP, hoặc cơ sở dữ liệu lớn thường không hỗ trợ ký tự Unicode đầy đủ. Việc chuyển sang không dấu giúp tránh lỗi khi nhập dữ liệu vào hệ thống đó.
Trình bày báo cáo: Khi xuất dữ liệu sang PDF, Word, hoặc các trang web, dấu tiếng Việt có thể gây khó đọc hoặc mất định dạng. Loại bỏ dấu giúp báo cáo trông gọn gàng hơn.
Có thể bạn quan tâm: Cách Chia Giấy A4 Thành 4 Phần Trong Excel
2. Cách chuyển ký tự có dấu sang không dấu bằng công thức Excel
2.1 Phương pháp 1: Sử dụng hàm SUBSTITUTE lặp
Hàm SUBSTITUTE trong Excel cho phép thay thế một chuỗi con trong một chuỗi lớn. Bằng cách kết hợp nhiều lần SUBSTITUTE, chúng ta có thể thay thế từng ký tự có dấu bằng ký tự không dấu tương ứng.
Ví dụ:
Giả sử ô A1 chứa chuỗi: “Bà Trần Thị Lệ”. Chúng ta muốn chuyển thành “Ba Tran Thi Le”.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE prosjekt(A1, "á","a"), "à","a"), "ả","a"), "ã","a"), "ạ","a"), "ă","a"), "ắ","a"), "ằ","a"), "ẳ","a"), "ẵ","a"), "ặ","a"), "â","a"), "ấ","a"), "ầ","a"), "ẩ","a"), "ẫ","a"), "ậ","a"), "đ","d"), "é","e"), "è","e"), "ẻ","e"), "ẽ","e"), "ẹ","e"), "ê","e"), "ế","e"), "ề","e"), "ể","e"), "ễ","e"), "ệ","e"), "í","i"), "ì","i"), "ỉ","i"), "ĩ","i"), "ị","i"), "ó","o"), "ò","o"), "ỏ","o"), "õาน","o"), "ọ","o"), "ô","o"), "ố","o"), "ồ","o"), "ổ","o"), "ỗ","o"), "ộ","o"), "ơ","o"), "ớ","o"), "ờ","o"), "ở","o"), "ỡ","o"), "ợ","o"), "ú","u"), "ù","u"), "ủ","u"), "ũ","u"), "ụ","u"), "ư","u"), "ứ","u"), "ừ entregu", "ử","u"), "ữ","u"), "ự","u"), "ý"," dlou"), "ỳ","y"), "ỷ","y"), "ỹ","y"), "ỵ","y"),A1,""))
Lưu ý: Công thức trên thực chất là một chuỗi lồng nhau của
SUBSTITUTE. Số lượngSUBSTITUTEcó thể lên tới 70+ tùy vào số ký tự có dấu cần thay thế. Đây là phương pháp đơn giản nhưng rất “đắt” tính toán và khó bảo trì.
2.2 Phương pháp 2: Sử dụng hàm CHAR và CODE (định dạng ASCII)
Một cách khác là chuyển chuỗi sang dạng Unicode, sau đó loại bỏ dấu bằng cách trừ đi giá trị_flush_ nhất định. Tuy nhiên, Excel không cung cấp hàm trực tiếp để thực hiện việc này, nên ta cần viết một hàm tùy chỉnh bằng VBA (không thể làm bằng công thức thuần).
Có thể bạn quan tâm: Cách Chia Một Hàng Thành Hai Hàng Trong Excel: Hướng Dẫn Chi Tiết Và Các Mẹo Hữu Ích
3. Viết hàm tùy chỉnh bằng VBA để chuyển ký tự có dấu sang không dấu
VBA cung cấp khả năng truy cập trực tiếp vào các hàm Unicode, tạo ra một hàm thực thi nhanh hơn và dễ dàng bảo trì. Dưới đây là một ví dụ hàm RemoveDiacritics:
3.1 Mã VBA
Function RemoveDiacritics(ByVal Text As String) As String Dim i As Long, c As Long Dim res As String For i = 1 To Len(Text) c = AscW(Mid(Text, i, 1)) ' Lấy mã Unicode Select Case c ' Kiểm tra các mã Unicode của tiếng Việt và thay thế Case 192 To 255 res = res & UnicodetoAscii(c) Case Else res = res & Mid(Text, i, 1) End Select Next i RemoveDiacritics = res
End Function Private Function UnicodetoAscii(ByVal Code As Long) As String Select Case Code Case 192 To 220: UnicodetoAscii = "A" 'ÁÀẢÃẠĂẮẰẲẴẶÂẤẦẨẪẬ Case 224 To 255: UnicodetoAscii = "a" 'áàảãạăắằẳẵặâấầẩẫậ Case 192 To 214, 216 To 222: UnicodetoAscii = "D" 'Đ Case 224 To 246, 248 To 254: UnicodetoAscii = "d" Case 200 To 202: UnicodetoAscii = "E" 'ÉÈẺẼẸÊẾỀỂỄỆ Case 232 To 234: UnicodetoAscii = "e" Case 204 To 206: UnicodetoAscii = "I" 'ÍÌỈĨỊ Case 236 To 238: UnicodetoAscii = "i" Case 210 To 214, 216 To 222: UnicodetoAscii = "O" 'ÓÒỎÕỌÔỐỒỔỖỘƠỚỜỞỠỢ Case 242 To 246, 248 To 254: UnicodetoAscii = "o" Case 218 To 220, 222 To 224: UnicodetoAscii = "U" 'ÚÙỦŨỤƯỨỪỬỮỰ Case 250 To 254, 256 To 258: UnicodetoAscii = "u" Case 221 To 222: UnicodetoAscii = "Y" 'ÝỲỶỸỴ Case 253 To 254: UnicodetoAscii = "y" Case Else: UnicodetoAscii = ChrW(Code) ' Khác không thay thế End Select
End Function
Cách sử dụng:
1. Mở Excel, nhấnAlt + F11để mở cửa sổ VBA.
2. Chèn một module mới (Insert > Module).
3. Dán mã trên vào cửa sổ module.
4. Quay lại Excel, sử dụng hàm:=RemoveDiacritics(A1).
3.2 Ưu điểm
- Tốc độ nhanh hơn so với chuỗi
SUBSTITUTElồng nhau. - Dễ bảo trì: Bạn chỉ cần cập nhật hàm
UnicodetoAsciinếu có nhu cầu thay đổi. - Hỗ trợ liên tục: Không bị giới hạn bởi số lượng ký tự có dấu.
4. Sử dụng Power Query để chuyển ký tự có dấu sang không dấu
Power Query (được tích hợp trong Excel 2026 trở đi, hoặc làm add-in cho Excel 2026/2026) hỗ trợ viết các bước chuyển đổi dữ liệu bằng giao diện kéo‑thả, hoặc viết mã M. Dưới đây là cách thực hiện:
4.1 Bước 1: Nhập dữ liệu vào Power Query
- Chọn vùng dữ liệu, vào
Data > Get & Transform Data > From Table/Range. - Đảm bảo dữ liệu có header.
4.2 Bước 2: Thêm cột tùy chỉnh
Có thể bạn quan tâm: Cách Chuyển Chữ Từ Ngang Sang Dọc Trong Excel: Hướng Dẫn Chi Tiết Và Mẹo Thực Tiễn
Trong Power Query Editor, vào Add Column > Custom Column. Ghi công thức M:
Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace(Text.Replace preprocess
Lưu ý: Mã M có thể tốn công sức so với VBA, nhưng nó phù hợp khi bạn cần thực hiện nhiều bước sạch dữ liệu khác nhau trong một pipeline.
5. Giải pháp(Hash) lưu trữ dữ liệu không dấu trong bảng tính
Trong một số trường hợp, bạn muốn đồng bộ dữ liệu giữa bảng tính và một hệ thống database. Thay vì lưu dữ liệu gốc, bạn có thể lưu trữ một phiên bản không dấu trong một cột phụ, giúp nhanh chóng tìm kiếm, làm trùng dữ liệu.
5.1 Cách thực hiện
- Giả sử cột A chứa tên có dấu.
- Tạo cột B với công thức:
- Nếu dùng VBA:
=RemoveDiacritics(A2) Nếu dùng công thức:
=SUBSTITUTE(...)nad.Sử dụng
VLOOKUPhoặcMATCHdựa vào cột B để tìm kiếm.
5.2 Lợi ích
- Tăng tốc khi làm việc với bảng tính lớn (thousands rows).
- Đảm bảo tính nhất quán khi nhập dữ liệu từ nhiều nguồn.
6. Các mẹo nhỏ khi làm việc với ký tự có dấu trong Excel
| Mẹo | Mô tả | Ví dụ |
|---|---|---|
Sử dụng TEXTJOIN birbir | Kết hợp chuỗi sau khi loại bỏ dấu | =TEXTJOIN(" ",TRUE,RemoveDiacritics(A2:A10)) |
Dùng TRIM | Loại bỏ dấu cách thừa | =TRIM(RemoveDiacritics(A1)) |
Sử dụng FIND/SEARCH | Tìm kiếm chuỗi không dấu | =SEARCH("tranh",B1) |
| Z-Table | Tạo bảng ánh xạ từ ký tự có dấu → không dấu | =VLOOKUP(MID(A1,1,1),ZTable,2,FALSE) |
| Mã Unicode | Kiểm tra mã Unicode của ký tự | =CODE(MID(A1,1,1)) |
7. Lưu ý khi xử lý dữ liệu tiếng Việt
Unicode vs ANSI: Excel hiện tại hỗ trợ Unicode, nhưng khi lưu file .xlsc (cũ), một số ký tự có thể bị mất. Hãy lưu file dưới định dạng .xlsx hoặc .xlsm.
Ngôn ngữ hệ thốngEditor’s: Nếu hệ thống máy tính chưa cài đặt tiếng Việt, các ký tự có dấuڑ có thể hiển thị sai. Đảm bảo cài đặt ngôn ngữ tiếng Việt trong Control Panel.
Định dạng số & ngày: Khi chuyển đổi dữ liệu, đừng quên kiểm tra lại định dạng số, ngày tháng, vì việc chuyển đổi chuỗi có thể làm mất định dạng gốc.
Kiểm tra dữ liệu sau khi chuyển: Luôn thực hiện kiểm tra lại dữ liệu sau khi áp dụng công thức hoặc VBA, đặc biệt là khi dữ liệu chứa chữ số hoặc ký tự đặc biệt.
8. Bài tập thực hành
Bài tập 1: Tạo một bảng gồm 50 tên tiếng Việt có dấu. Viết công thức
SUBSTITUTElồng nhau để chuyển thành không dấu. Ghi-বিনাস.Bài tập 2: Sử dụng VBA viết hàm
RemoveDiacriticsvà áp dụng cho cột A. So sánh thời gian thực thi với công thứcSUBSTITUTE.Bài tập 3: Sử dụng Power Query để tạo cột không dấu và sau đó sắp xếp dữ liệu theo cột đó.
Bài tập 4: Tạo bảng “Z-Table” ánh xạ ký tự có dấu → không dấu, và viết công thức sử dụng
VLOOKUPđể chuyển đổi.
9. Tóm tắt các phương pháp
| Phương pháp | Ưu điểm | Nhược điểm | Thời gian áp dụng |
|---|---|---|---|
| SUBSTITUTE lồng nhau | Không cần VBA, dễ hiểu | Dễ lỗi, công thức dài, chậm | Dùng khi số dữ liệu nhỏ |
| VBA (RemoveDiacritics) | Nhanh, dễ bảo trì | Cần bật macro, không chạy trên online | Dùng cho dữ liệu lớn |
| Power Query | Tích hợp trong Excel, linh hoạt | Đòi hỏi kiến thức M | Dùng khi cần pipeline sạch dữ liệu |
| Z-Table + VLOOKUP | Dễ mở rộng, có thể dùng trong nhiều trường hợp | Cần tạo bảng ánh xạ | Dùng khi cần lưu trữ không dấu |
10. Kết luận
Việc chuyển ký tự có dấu sang không dấu trong Excel là một thao tác thiết yếu trong quá trình làm sạch dữ liệu, chuẩn hoá dữ liệu và tối ưu tìm kiếm. Dù bạn là người mới bắt đầu hay là nhà phân tích dữ liệu chuyên nghiệp, hãy lựa chọn phương pháp phù hợp với quy mô dữ liệu và khả năng kỹ thuật của mình.
- Nếu bạn chỉ thao tác với vài dòng dữ liệu,
SUBSTITUTElồng nhau có thể đủ. - Nếu phải xử lý hàng nghìn dòng, nên dùng VBA hoặc Power Query để tăng tốc.
- Đối với các nghiệp vụ lâu dài, hãy lưu trữ một cột không dấu và dùng nó trong các hàm tìm kiếm, sắp xếp.
Nhớ kiểm tra kỹ dữ liệu sau khi chuyển, vì việc loại bỏ dấu có thể làm mất một phần ngữ nghĩa trong văn bản. Bằng cách kết hợp các công cụ và phương pháp nêu trên, bạn sẽ có thể quản lý dữ liệu tiếng Việt một cách hiệu quả, chính xác và nhanh chóng. Happy Excel-ing!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.