Hàm CODE Trong Excel: 5 Ứng Dụng Thực Tế Bạn Cần Biết

Đa số người dùng Excel biết hàm CODE trả về mã số của ký tự, nhưng rất ít người áp dụng nó vào công việc thực tế. Hàm này thường xuất hiện trong các ví dụ cơ bản như CODE(“A”) trả về 65, rồi dừng lại ở đó. Thực tế, CODE giải quyết được nhiều vấn đề mà các hàm khác bó tay hoàn toàn.

Mục Lục

Xử Lý Khoảng Trắng Mà TRIM Không Xóa Được

Đây là tình huống tôi gặp thường xuyên nhất. Khi copy dữ liệu từ web hoặc PDF vào Excel, hàm TRIM không xóa được khoảng trắng dù nhìn có vẻ giống khoảng cách thông thường. Nguyên nhân là TRIM chỉ xóa khoảng trắng có mã 32 trong bảng mã ANSI, còn nhiều loại khoảng trắng khác như mã 160 (non-breaking space) hoàn toàn không bị ảnh hưởng.

Cách phát hiện loại khoảng trắng:

Giả sử ô A2 chứa văn bản có khoảng trắng lạ. Sử dụng công thức:

=CODE(MID(A2,FIND(" ",A2),1))

Nếu kết quả là 160 thay vì 32, đó chính là non-breaking space. TRIM sẽ không xóa được loại này.

Giải pháp với hàm SUBSTITUTE kết hợp CODE:

=SUBSTITUTE(A2,CHAR(160),CHAR(32))

Công thức này chuyển non-breaking space (mã 160) thành khoảng trắng thông thường (mã 32). Sau đó áp dụng TRIM để loại bỏ khoảng trắng thừa:

=TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))

Tôi xử lý bảng dữ liệu khách hàng 500 dòng import từ CRM trong 2 phút với công thức này, thay vì phải sửa thủ công từng ô. TRIM đơn thuần không làm được gì vì toàn bộ khoảng trắng đều là mã 160.

Áp dụng cho nhiều loại khoảng trắng:

Nếu dữ liệu có nhiều loại khoảng trắng khác nhau, kết hợp nhiều SUBSTITUTE:

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(202)," "))

Mã 202 là em-space, một loại khoảng trắng khác thường xuất hiện trong văn bản tiếng Anh. Công thức trên chuyển tất cả về khoảng trắng chuẩn rồi dùng TRIM dọn sạch.

XEM THÊM: Hàm QUARTILE.EXC Trong Excel: Hướng Dẫn Chi Tiết Từ Cú Pháp Đến Ứng Dụng Thực Tế

So Sánh Văn Bản Có Phân Biệt Chữ Hoa Chữ Thường

Excel không phân biệt chữ hoa chữ thường khi so sánh văn bản thông thường. Công thức =A1=B1 trả về TRUE ngay cả khi A1 chứa “Excel” và B1 chứa “EXCEL”. Điều này gây sai sót khi cần kiểm tra mật khẩu, mã sản phẩm, hoặc bất kỳ dữ liệu nào nhạy cảm với chữ hoa chữ thường.

So sánh chính xác với CODE:

Hàm CODE kết hợp EXACT giải quyết vấn đề này. Mã ANSI của “A” là 65, của “a” là 97. CODE nhận ra sự khác biệt này.

=CODE(A1)=CODE(B1)

Công thức trên chỉ trả về TRUE khi ký tự đầu tiên của cả hai ô hoàn toàn giống nhau, bao gồm cả chữ hoa chữ thường.

Kiểm tra toàn bộ chuỗi:

Để so sánh toàn bộ chuỗi thay vì chỉ ký tự đầu, kết hợp với SUMPRODUCT:

=SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))=SUMPRODUCT(CODE(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)))

Công thức này chuyển mỗi ký tự trong chuỗi thành mã số, cộng tất cả lại và so sánh tổng. Nếu hai chuỗi giống hệt nhau từng ký tự, tổng mã số sẽ bằng nhau.

Ứng dụng validate mật khẩu:

Tạo cột kiểm tra mật khẩu nhập lại có khớp với mật khẩu gốc không:

=IF(SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))=SUMPRODUCT(CODE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))),"Khớp","Không khớp")

Phương pháp này chính xác hơn so sánh văn bản thông thường vì Excel mặc định không phân biệt chữ hoa chữ thường.

Tìm Và Thay Thế Ký Tự Không Nhìn Thấy

Các ký tự điều khiển như xuống dòng (line break), tab, hoặc carriage return thường gây rối trong dữ liệu nhưng không nhìn thấy bằng mắt thường. Chúng xuất hiện khi import dữ liệu từ database, copy từ notepad, hoặc nhận dữ liệu từ API.

Phát hiện ký tự xuống dòng:

Ký tự xuống dòng trong Excel có mã 10. Để tìm xem ô nào chứa xuống dòng:

=IF(ISNUMBER(FIND(CHAR(10),A2)),"Có xuống dòng","Không có")

Xóa tất cả xuống dòng:

=SUBSTITUTE(A2,CHAR(10),"")

Nếu muốn thay xuống dòng bằng dấu cách để giữ nguyên văn bản trên một dòng:

=SUBSTITUTE(A2,CHAR(10)," ")

Xử lý nhiều ký tự đặc biệt cùng lúc:

XEM THÊM: Hàm LARGE Giúp Tôi Xếp Hạng 500 Nhân Viên Trong 3 Giây

Dữ liệu từ nguồn ngoài thường chứa cả xuống dòng (mã 10), carriage return (mã 13), và tab (mã 9). Xóa tất cả bằng công thức lồng SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),""),CHAR(13),""),CHAR(9)," "))

Công thức này loại bỏ xuống dòng và carriage return hoàn toàn, đồng thời thay tab bằng khoảng trắng.

Tìm mã số của ký tự lạ:

Khi gặp ký tự không xác định được, dùng CODE để tìm mã số của nó:

=CODE(MID(A2,5,1))

Công thức trên trả về mã số của ký tự ở vị trí thứ 5 trong ô A2. Sau khi biết mã số, dùng CHAR trong SUBSTITUTE để xóa hoặc thay thế.

Tôi xử lý file CSV từ hệ thống cũ với 10,000 dòng chứa đầy ký tự điều khiển trong 5 phút nhờ công thức này. Trước đây phải mở bằng text editor và tìm thay thế thủ công mất cả buổi sáng.

Kiểm Tra Ký Tự Cho Phép Trong Nhập Liệu

Khi thiết kế form nhập liệu trong Excel, cần đảm bảo người dùng chỉ nhập ký tự hợp lệ. Ví dụ như mã sản phẩm chỉ chứa chữ in hoa và số, không có ký tự đặc biệt.

Kiểm tra chỉ chứa chữ cái và số:

Dùng CODE để xác định ký tự nằm trong phạm vi cho phép. Chữ in hoa có mã từ 65 đến 90, chữ thường từ 97 đến 122, số từ 48 đến 57.

=IF(SUMPRODUCT((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=65)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<=90)+(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=48)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<=57))=LEN(A2),"Hợp lệ","Có ký tự không hợp lệ")

Công thức này kiểm tra từng ký tự trong chuỗi. Nếu tất cả ký tự đều nằm trong phạm vi cho phép, trả về “Hợp lệ”.

Tìm vị trí ký tự không hợp lệ:

Thay vì chỉ thông báo có lỗi, tìm chính xác ký tự nào và ở đâu:

=IFERROR(MATCH(FALSE,(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=65)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<=90)+(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=48)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<=57),0),"Tất cả hợp lệ")

Kết quả trả về vị trí của ký tự đầu tiên không hợp lệ trong chuỗi. Người dùng biết chính xác cần sửa ở đâu.

Áp dụng cho form nhập liệu:

Kết hợp với Data Validation để cảnh báo ngay khi người dùng nhập sai:

Chọn vùng cần validate
Data > Data Validation
Allow: Custom
Formula: Dán công thức kiểm tra ở trên

Excel sẽ không cho phép người dùng nhập vào ô nếu dữ liệu không hợp lệ. Điều này giảm thiểu sai sót từ đầu thay vì phải kiểm tra sau.

XEM THÊM: Tôi Hiểu Sai Hoàn Toàn Hàm KURT Cho Đến Khi Phân Tích Dữ Liệu Doanh Số

Tạo Mã Định Danh Từ Văn Bản

Nhiều hệ thống yêu cầu mã định danh duy nhất cho sản phẩm, khách hàng, hoặc đơn hàng. Thay vì tạo thủ công, dùng CODE để chuyển văn bản thành chuỗi số làm phần của mã.

Chuyển tên thành mã số:

Giả sử cần tạo mã sản phẩm từ tên. Lấy tổng mã ASCII của tất cả ký tự trong tên:

=SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

Ví dụ: “Laptop” có tổng mã là 76+97+112+116+111+112 = 624. Mỗi tên sẽ có tổng mã khác nhau (trừ khi là anagram hoàn hảo).

Tạo mã với tiền tố:

Kết hợp với các thông tin khác để tạo mã hoàn chỉnh:

="SP"&TEXT(SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),"00000")&TEXT(ROW(),"000")

Công thức này tạo mã có dạng SP00624001, trong đó:

SP là tiền tố cố định
00624 là tổng mã ASCII của tên (pad thêm số 0 phía trước)
001 là số thứ tự dòng

Xử lý trùng lặp:

Vì tổng mã ASCII có thể trùng nhau, thêm phần timestamp hoặc random:

="SP"&TEXT(SUMPRODUCT(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),"00000")&TEXT(NOW(),"hhmmss")

Mã bây giờ chứa cả giờ phút giây, đảm bảo duy nhất ngay cả khi tên trùng nhau.

Ứng dụng thực tế:

Tôi dùng phương pháp này để tạo mã tracking cho 2,000 đơn hàng mỗi tháng. Hệ thống tự động sinh mã từ tên khách hàng cộng với timestamp, không bao giờ trùng lặp. Trước đây phải nhập thủ công hoặc dùng macro phức tạp.

Tương Thích Và Lưu Ý

Hàm CODE hoạt động trên tất cả phiên bản Excel từ 2007 trở về sau, bao gồm Excel 365, Excel 2019, 2016, 2013, và 2010. Hàm sử dụng bảng mã ANSI trên Windows và bảng mã Macintosh trên Mac, nhưng các mã số phổ biến như chữ cái và số thì giống nhau trên cả hai hệ điều hành.

Lưu ý rằng CODE chỉ trả về mã số của ký tự đầu tiên trong chuỗi. Nếu cần xử lý toàn bộ chuỗi, kết hợp với MID và ROW như các ví dụ ở trên. Khi ô trống, CODE trả về lỗi #VALUE!, nên bọc công thức trong IFERROR nếu dữ liệu có thể chứa ô trống.

Hàm CHAR là đối tác của CODE, chuyển mã số ngược lại thành ký tự. Hai hàm này thường được dùng kết hợp để xử lý ký tự đặc biệt trong Excel một cách chuyên nghiệp.