Có thể bạn quan tâm: Đánh Số Thứ Tự Excel: Cách Thực Hiện Và Ứng Dụng Thực Tế
Lời mở đầu
Trong môi trường làm việc với dữ liệu, việc xử lý và phân tích văn bản là một trong những nhiệm vụ thường gặp. Đặc biệt, trong Microsoft Excel – một công cụ mạnh mẽ và phổ biến cho việc quản lý dữ liệu – chúng ta thường cần đếm số lượng ký tự trong một ô hoặc một dải ô để thực hiện kiểm tra độ dài, chuẩn hoá dữ liệu, hoặc tạo ra các công thức phức tạp hơn. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ những kiến thức cơ bản về hàm LEN cho tới các kỹ thuật nâng cao như kết hợp SUBSTITUTE, TRIM, MID, LEFT, RIGHT, và các hàm mảng để đếm ký tự đặc biệt, ký tự Unicode, hoặc ký tự trong các ngôn ngữ không phải Latin.
Mục tiêu: Sau khi đọc bài viết, bạn sẽ nắm vững cách sử dụng các hàm đếm ký tự trong Excel, biết cách xử lý các trường hợp thực tế như loại bỏ khoảng trắng, đếm ký tự trong chuỗi đa dòng, và áp dụng các công thức trong bảng tính lớn mà không làm giảm hiệu suất.
Có thể bạn quan tâm: Cách Tạo Lựa Chọn Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Tổng quan về hàm LEN
1.1 Cú pháp và cách hoạt động
LEN(text)
- text: là chuỗi ký tự, ô tham chiếu, hoặc biểu thức trả về chuỗi mà bạn muốn đếm số ký tự.
- Kết quả trả về là một số nguyên, biểu thị tổng số ký tự trong chuỗi, bao gồm cả khoảng trắng và ký tự đặc biệt (dấu câu, ký tự Unicode, v.v.).
Ví dụ:
| A | Công thức | Kết quả |
|---|---|---|
| Hello World! | =LEN(A2) | 12 |
| Excel (có 3 khoảng trắng ở đầu và cuối) | =LEN(A3) | 13 |
| 12345 | =LEN(A4) | 5 |
| (rỗng) | =LEN(A5) | 0 |
1.2 Lưu ý quan trọng
- Ký tự Unicode: Excel tính mỗi ký tự Unicode là một ký tự, kể cả những ký tự đa byte như tiếng Trung, tiếng Nhật, hoặc emoji. Ví dụ, emoji 😊 được tính là 1 ký tự trong
LEN. - Dòng mới (Line break): Khi một ô chứa ký tự xuống dòng (Alt+Enter),
LENcũng sẽ tính ký tự này (ASCII 10) như một ký tự. - Khoảng trắng: Các ký tự space (
) và tab (CHAR(9)) được tính trong kết quả.
Có thể bạn quan tâm: Cách Tính Phần Trăm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Đếm ký tự không tính khoảng trắng
Trong nhiều trường hợp, chúng ta muốn bỏ qua các khoảng trắng ở đầu, cuối hoặc trong chuỗi. Dưới đây là một số kỹ thuật phổ biến.
2.1 Sử dụng TRIM + LEN
=LEN(TRIM(text))
TRIMloại bỏ khoảng trắng ở đầu và cuối và giảm nhiều khoảng trắng liên tiếp thành một.- Khi kết hợp với
LEN, chúng ta nhận được độ dài của chuỗi đã được chuẩn hoá.
Ví dụ:
| A | Công thức | Kết quả |
|---|---|---|
| ” Excel ” (có 3 space đầu, 3 space cuối) | =LEN(TRIM(A2)) | 5 |
| ” Hello World “ | =LEN(TRIM(A3)) | 11 (giảm thành “Hello World”) |
2.2 Loại bỏ tất cả khoảng trắng (kể cả trong chuỗi)
Nếu muốn đếm ký tự không tính bất kỳ khoảng trắng nào (cả trong giữa), chúng ta dùng SUBSTITUTE để thay thế space bằng chuỗi rỗng.
=LEN(SUBSTITUTE(text, " ", ""))
Ví dụ:
| A | Công thức | Kết quả |
|---|---|---|
| “Hello World!” | =LEN(SUBSTITUTE(A2, ” “, “”)) | 11 |
| ” A B C “ | =LEN(SUBSTITUTE(A3, ” “, “”)) | 3 |
Mở rộng: Để loại bỏ cả tab (
CHAR(9)) và dòng mới (CHAR(10)), chúng ta lồng nhiềuSUBSTITUTE:
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text, " ", ""), CHAR(9), ""), CHAR(10), ""))
3. Đếm ký tự đặc biệt
3.1 Đếm số lần xuất hiện của một ký tự cụ thể
Để đếm số lần một ký tự (ví dụ: dấu phẩy ,) xuất hiện trong một ô, chúng ta so sánh độ dài trước và sau khi loại bỏ ký tự đó:
=LEN(text) - LEN(SUBSTITUTE(text, "ký_tự_cần_đếm", ""))
Ví dụ:
| A | Công thức | Kết quả |
|---|---|---|
| “a,b,c,d,e” | =LEN(A2) – LEN(SUBSTITUTE(A2, “,”, “”)) | 4 |
| “Hello…World!” | =LEN(A3) – LEN(SUBSTITUTE(A3, “…”, “”)) | 1 (đếm 3 dấu chấm liên tiếp) |
3.2 Đếm ký tự không phải chữ hoặc số (ký tự đặc biệt)
Sử dụng hàm mảng kết hợp MID, ROW, và ISNUMBER:
=SUM(--ISERROR(--MID(text, ROW(INDIRECT("1:"&LEN(text))), 1)))
Giải thích:
ROW(INDIRECT("1:"&LEN(text)))tạo một mảng các vị trí 1…n.MID(text, vị_trí, 1)trả về từng ký tự.--chuyển ký tự thành số (nếu là số) hoặc trả về lỗi.ISERRORtrả về TRUE cho ký tự không phải số.SUMcộng các TRUE (được ép 1) => số ký tự không phải số.
Lưu ý: Công thức trên là công thức mảng (Excel 365/2026 có thể nhấn Enter bình thường; phiên bản cũ cần Ctrl+Shift+Enter).
3.3 Đếm ký tự Unicode cao (emoji, ký tự đa byte)
Excel tính mỗi ký tự Unicode là 1, nhưng một số emoji thực tế được biểu diễn bằng cặp surrogate (2 ký tự). Để đếm số emoji thực sự, chúng ta cần kiểm tra giá trị Unicode.
Ví dụ, để đếm emoji trong ô A2 (các ký tự có mã Unicode > 65535):
=SUM(--(UNICODE(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1))>65535))
Cảnh báo: Hàm
UNICODEchỉ hỗ trợ ký tự BMP (Basic Multilingual Plane) trong một số phiên bản Excel. Đối với Excel 365,UNICODEhoạt động tốt.
4. Đếm ký tự trong dải ô (range)
4.1 Tổng số ký tự của một cột
=SUMPRODUCT(LEN(A:A))
LEN(A:A)trả về một mảng độ dài của từng ô trong cột A.SUMPRODUCTcộng lại.
Lưu ý: Tránh dùng toàn bộ cột (A:A) nếu bảng dữ liệu lớn; thay vào đó, chỉ định phạm vi thực tế (A2:A1000) để tăng hiệu suất.
4.2 Đếm ký tự thỏa mãn điều kiện
Giả sử muốn tính tổng ký tự của các ô trong cột A mà có chứa từ “Excel”:
=SUMPRODUCT(LEN(A2:A1000)(ISNUMBER(SEARCH("Excel", A2:A1000))))
SEARCHtrả về vị trí nếu tìm thấy, ngược lại trả về lỗi.ISNUMBERchuyển thành TRUE/FALSE.- Nhân với
LENđể chỉ tính độ dài của các ô thỏa điều kiện.
4.3 Đếm ký tự không tính khoảng trắng trong dải ô
Có thể bạn quan tâm: Đặt Mật Khẩu Cho File Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
=SUMPRODUCT(LEN(SUBSTITUTE(A2:A1000, " ", "")))
Hoặc nếu muốn loại bỏ tất cả ký tự trắng (space, tab, line break):
=SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A1000, " ", ""), CHAR(9), ""), CHAR(10), "")))
5. Thực hành: Kiểm tra độ dài mật khẩu
Một bài toán thực tế thường gặp là xác định độ mạnh mật khẩu dựa trên độ dài và sự hiện diện của ký tự đặc biệt. Dưới đây là một công thức tổng hợp:
=IF(LEN(A2)<8, "Yếu", IF(AND(LEN(A2)>=8, LEN(SUBSTITUTE(A2," ",""))=LEN(A2), // không có space SUM(--ISNUMBER(FIND(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1), "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"))) < LEN(A2)), "Trung bình", "Mạnh"))
Giải thích nhanh:
- Kiểm tra độ dài ít nhất 8 ký tự.
- Đảm bảo không có khoảng trắng.
- Đếm số ký tự không phải chữ hoặc số (kí tự đặc biệt). Nếu có ít nhất một ký tự đặc biệt => “Mạnh”.
Lưu ý: Công thức trên là công thức mảng; trong Excel 365 chỉ cần Enter.
6. Các vấn đề thường gặp và cách khắc phục
| Vấn đề | Mô tả | Giải pháp |
|---|---|---|
| Kết quả trả về 0 cho ô chứa ký tự Unicode | Một số phiên bản Excel cũ không nhận dạng đúng Unicode trong LEN. | Nâng cấp lên Excel 2026 trở lên hoặc sử dụng VBA (LenB) để tính byte. |
| Công thức mảng chậm | Khi áp dụng LEN + ROW(INDIRECT(...)) trên dải lớn, hiệu suất giảm. | Sử dụng LET (Excel 365) để lưu mảng tạm, hoặc chuyển sang Power Query. |
| Đếm ký tự trong ô chứa công thức | LEN trả về độ dài của kết quả hiển thị, không phải công thức. | Không cần thay đổi; nếu muốn đếm độ dài công thức, dùng FORMULATEXT + LEN. |
| Khoảng trắng không mong muốn | TRIM chỉ loại bỏ space, không loại bỏ tab hoặc line break. | Kết hợp SUBSTITUTE với CHAR(9) và CHAR(10). |
| Dòng mới (Alt+Enter) không được tính | Một số người dùng nghĩ LEN bỏ qua line break. | Thực tế LEN tính CHAR(10). Nếu muốn bỏ qua, dùng SUBSTITUTE(text, CHAR(10), ""). |
7. Sử dụng VBA để đếm ký tự nâng cao
Khi công thức trở nên quá phức tạp hoặc khi cần đếm ký tự trong hàng nghìn ô một cách nhanh chóng, VBA là giải pháp tối ưu.
7.1 Hàm VBA đếm ký tự (bao gồm tùy chọn loại bỏ khoảng trắng)
Function CountChars(ByVal rng As Range, Optional RemoveSpaces As Boolean = False) As Long Dim cell As Range Dim txt As String Dim total As Long For Each cell In rng If Not IsError(cell.Value) Then txt = CStr(cell.Value) If RemoveSpaces Then txt = Replace(txt, " ", "") total = total + Len(txt) End If Next cell CountChars = total
End Function
- Cách dùng:
=CountChars(A2:A1000, TRUE)sẽ trả về tổng ký tự không tính khoảng trắng. - Ưu điểm: Xử lý nhanh, không giới hạn độ dài công thức.
7.2 Đếm số ký tự đặc biệt (không phải chữ, không phải số)
Function CountSpecialChars(ByVal txt As String) As Long Dim i As Long, ch As String, cnt As Long For i = 1 To Len(txt) ch = Mid(txt, i, 1) If Not (ch Like "A-Za-z0-9") Then cnt = cnt + 1 Next i CountSpecialChars = cnt
End Function
- Cách dùng:
=CountSpecialChars(A2).
7.3 Đếm Emoji (Unicode > 65535)
Function CountEmoji(ByVal txt As String) As Long Dim i As Long, code As Long, cnt As Long For i = 1 To Len(txt) code = AscW(Mid(txt, i, 1)) If code > 65535 Then cnt = cnt + 1 Next i CountEmoji = cnt
End Function
- Cách dùng:
=CountEmoji(A2).
Lưu ý: Để sử dụng các hàm VBA, bạn cần mở Developer → Visual Basic, chèn Module, dán code và lưu file dưới dạng .xlsm.
8. Thực hành trong Power Query (Get & Transform)
Trong môi trường dữ liệu lớn, Power Query (được tích hợp trong Excel 2026 trở lên) cung cấp công cụ mạnh mẽ để xử lý chuỗi.
8.1 Đếm ký tự trong Power Query
- Chọn Data → Get Data → From Table/Range.
- Trong Power Query Editor, thêm cột tùy chỉnh:
- Custom Column Formula:
Text.Length(ColumnName) - Đặt tên cột mới, ví dụ “Length”.
8.2 Đếm ký tự không tính khoảng trắng
Công thức tùy chỉnh:
Text.Length(Text.Remove(ColumnName, {" "}))
8.3 Đếm ký tự đặc biệt
List.Count( List.Select( Text.ToList(ColumnName), each not Text.ContainsAny(_, {"0".."9","a".."z","A".."Z"}) )
)
Power Query cho phép xử lý dữ liệu hàng triệu dòng mà không làm chậm Excel.
9. Kiểm tra chất lượng dữ liệu bằng hàm đếm ký tự
9.1 Kiểm tra độ dài chuẩn của mã sản phẩm
Giả sử mã sản phẩm phải có độ dài 10 ký tự và không chứa khoảng trắng.
Công thức:
=IF(AND(LEN(A2)=10, LEN(SUBSTITUTE(A2," ",""))=10), "OK", "LỖI")
9.2 Xác định các ô có nội dung trùng lặp độ dài
=COUNTIF(LEN($A$2:$A$1000), LEN(A2))>1
- Nếu trả về TRUE, nghĩa là có ít nhất một ô khác có cùng độ dài.
9.3 Đánh dấu các ô vượt quá giới hạn ký tự (ví dụ, mô tả sản phẩm tối đa 200 ký tự)
=IF(LEN(A2)>200, "Quá dài", "OK")
Bạn có thể áp dụng Conditional Formatting dựa trên công thức này để tự động tô màu các ô vi phạm.
10. Tối ưu hoá công thức cho bảng tính lớn
- Tránh sử dụng toàn bộ cột (
A:A) trongSUMPRODUCThoặcLEN. Chỉ định phạm vi thực tế. - Sử dụng hàm
LET(Excel 365) để lưu trữ kết quả trung gian, giảm tính toán lặp lại.
Ví dụ tính tổng ký tự không tính space trong cột A (A2:A10000):
=LET( rng, A2:A10000, noSpace, SUBSTITUTE(rng, " ", ""), SUMPRODUCT(LEN(noSpace))
)
- Sử dụng
TABLE(công cụ Data → What‑If Analysis → Data Table) để tính nhanh cho các biến đổi độ dài.
11. Kết luận
Việc đếm ký tự trong Excel không chỉ dừng lại ở hàm LEN đơn giản. Từ việc loại bỏ khoảng trắng, đếm ký tự đặc biệt, xử lý Unicode và emoji, cho tới việc áp dụng các công thức mảng, VBA, và Power Query, bạn có thể giải quyết hầu hết các yêu cầu thực tiễn trong công việc hàng ngày. Khi áp dụng đúng công cụ và kỹ thuật, bạn sẽ:
- Tiết kiệm thời gian: Công thức ngắn gọn hoặc macro VBA giúp tự động hoá quy trình.
- Nâng cao độ chính xác: Kiểm tra dữ liệu đầu vào, ngăn ngừa lỗi nhập liệu.
- Cải thiện hiệu suất: Sử dụng phạm vi cụ thể, hàm
LET, và Power Query để xử lý dữ liệu lớn.
Hãy thử áp dụng những kiến thức trên vào dự án của bạn, và đừng ngại sáng tạo thêm các công thức tùy chỉnh để đáp ứng nhu cầu riêng. Chúc bạn thành công trong việc khai thác sức mạnh của Excel để đếm ký tự một cách linh hoạt và hiệu quả!
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ẻ.