Cách Đếm Số Ký Tự Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Đếm Số Dòng Được Lọc Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Giới thiệu chung về việc đếm ký tự trong Excel

Trong công việc văn phòng, đặc biệt là khi xử lý dữ liệu trong Excel, việc biết được độ dài của một chuỗi ký tự (số ký tự) là một nhu cầu thường gặp. Bạn có thể cần:

• Kiểm tra độ dài mật khẩu để đảm bảo đáp ứng yêu cầu bảo mật.
• Giới hạn độ dài mô tả sản phẩm trong các hệ thống quản lý nội dung.
• Tính số ký tự còn lại khi nhập dữ liệu vào các trường có giới hạn ký tự (ví dụ: 255 ký tự trong một ô).
• Phân tích dữ liệu văn bản, như đếm số ký tự trong các bình luận, mô tả, hoặc địa chỉ email.

Microsoft Excel cung cấp một số hàm và công cụ mạnh mẽ giúp thực hiện việc này một cách nhanh chóng và chính xác. Bài viết dưới đây sẽ hướng dẫn chi tiết cách đếm số ký tự trong Excel, từ các hàm cơ bản như LEN cho tới các kỹ thuật nâng cao sử dụng ARRAYFORMULA, SUMPRODUCT, VBA và Power Query.

Có thể bạn quan tâm: Cách Đếm Giá Trị Độc Nhất (distinct) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Hàm LEN – Cơ bản nhất để đếm ký tự

2.1. Cú pháp và cách dùng

=LEN(text)

• text: Chuỗi ký tự hoặc ô chứa chuỗi cần đếm.
• Hàm trả về số ký tự trong chuỗi, bao gồm cả khoảng trắng và các ký tự đặc biệt.

2.2. Ví dụ thực tế

Lưu ý: LEN tính cả ký tự Unicode (đối với Excel 2026 trở lên). Đối với các ký tự đa byte như tiếng Trung, tiếng Nhật, hàm vẫn trả về số ký tự đúng.

2.3. Đếm ký tự không tính khoảng trắng

Nếu muốn bỏ qua khoảng trắng, bạn có thể kết hợp LEN với SUBSTITUTE:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

Công thức này sẽ trừ đi số lượng khoảng trắng trong chuỗi, cho ra số ký tự không tính khoảng trắng.

Có thể bạn quan tâm: Cách Tính Số Ngày Giữa Hai Ngày Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

3. Đếm ký tự trong nhiều ô cùng lúc

3.1. Tổng số ký tự trong một cột

Giả sử cột A chứa danh sách các mô tả sản phẩm, bạn muốn tính tổng số ký tự của tất cả các ô:

=SUMPRODUCT(LEN(A2:A100))

• SUMPRODUCT sẽ tính tổng của mảng các giá trị trả về bởi LEN.
• Thích hợp cho dải ô lớn, không cần nhập công thức mảng.

3.2. Đếm ký tự trong một dải ô nhưng chỉ tính các ô không trống

=SUMPRODUCT(--(A2:A100<>""),LEN(A2:A100))

• --(A2:A100<>"") tạo mảng 1/0 để chỉ tính các ô có nội dung.

3.3. Đếm ký tự theo tiêu chí (ví dụ: chỉ những ô chứa từ “Excel”)

=SUMPRODUCT(--(ISNUMBER(SEARCH("Excel",A2:A100))),LEN(A2:A100))

• SEARCH trả về vị trí nếu tìm thấy, ngược lại trả về lỗi. ISNUMBER chuyển thành TRUE/FALSE.

4. Đếm ký tự trong ô có công thức (kết quả hiển thị)

Khi ô chứa công thức, LEN sẽ đếm kết quả của công thức, không phải công thức gốc. Ví dụ:

• Ô B2: =A2 & " - Completed"
• =LEN(B2) sẽ trả về độ dài của chuỗi kết hợp, không phải độ dài của công thức =A2 & " - Completed".

Nếu muốn đếm độ dài công thức (đối với mục đích audit), cần dùng VBA hoặc Power Query (sẽ được trình bày sau).

5. Đếm ký tự Unicode và ký tự đa byte

5.1. Vấn đề với các ký tự đa byte

Trong Excel, một ký tự Unicode (như tiếng Trung, tiếng Nhật, emoji) vẫn được tính là 1 ký tự bởi hàm LEN. Tuy nhiên, một số ký tự đặc biệt như emoji có thể chiếm 2 “code units” trong UTF-16, khiến một số hàm (như LEN) trả về giá trị không mong muốn trong các phiên bản cũ.

5.2. Giải pháp

• Excel 2026 trở lên: LEN đã được cải thiện để trả về số ký tự thực tế.
• Sử dụng VBA: Hàm LenB trả về số byte, có thể chia cho 2 để ước lượng ký tự đa byte, nhưng không hoàn toàn chính xác.

Function LenUnicode(ByVal txt As String) As Long LenUnicode = Len(txt) - Len(Replace(txt, "?")) ' ? là ký tự thay thế
End Function

• Power Query: Khi nhập dữ liệu vào Power Query, bạn có thể dùng Text.Length để lấy độ dài chuẩn Unicode.

6. Đếm ký tự trong Power Query (Get & Transform)

Power Query là công cụ mạnh mẽ để xử lý dữ liệu trước khi tải vào bảng tính. Để đếm ký tự trong một cột:

1. Mở Power Query: Data > Get Data > From Table/Range.
2. Thêm cột tùy chỉnh: Add Column > Custom Column.
3. Nhập công thức:

= Text.Length(TênCột)

Kết quả sẽ là một cột mới chứa số ký tự của mỗi ô. Bạn có thể sau đó tổng hợp:

• Transform > Statistics > Sum để lấy tổng số ký tự của toàn cột.

Power Query xử lý Unicode một cách chuẩn, do đó các ký tự đa byte sẽ được tính đúng.

7. Sử dụng VBA để đếm ký tự (các trường hợp phức tạp)

Có thể bạn quan tâm: Hàm Random Không Lặp Lại Trong Excel: Cách Tạo Dữ Liệu Ngẫu Nhiên Độc Đáo, Ứng Dụng Thực Tiễn Và Mẹo Tối Ưu

7.1. Khi nào cần VBA?

• Đếm ký tự trong công thức (không phải kết quả).
• Đếm ký tự của các đối tượng như Shape, TextBox, Comment.
• Thực hiện đếm ký tự tự động khi người dùng nhập dữ liệu (event Worksheet_Change).

7.2. Ví dụ: Đếm ký tự trong công thức của một ô

Function FormulaLength(rng As Range) As Long If rng.HasFormula Then FormulaLength = Len(rng.Formula) Else FormulaLength = Len(rng.Value) End If
End Function

• Sử dụng trong ô: =FormulaLength(A2).

7.3. Ví dụ: Kiểm tra độ dài nhập trong thời gian thực

Private Sub Worksheet_Change(ByVal Target As Range) Dim MaxLen As Long: MaxLen = 50 ' Giới hạn ký tự If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then If Len(Target.Value) > MaxLen Then MsgBox "Bạn đã nhập quá " & MaxLen & " ký tự!", vbExclamation Application.EnableEvents = False Target.Value = Left(Target.Value, MaxLen) Application.EnableEvents = True End If End If
End Sub

Mã này sẽ giới hạn độ dài nhập trong cột B và hiển thị thông báo khi vượt quá.

8. Đếm ký tự trong các đối tượng không phải ô (Shape, TextBox, Comment)

8.1. Đếm ký tự trong Shape

Function ShapeTextLength(shpName As String) As Long Dim shp As Shape Set shp = ActiveSheet.Shapes(shpName) ShapeTextLength = Len(shp.TextFrame2.TextRange.Text)
End Function

8.2. Đếm ký tự trong Comment

Function CommentLength(cell As Range) As Long If Not cell.Comment Is Nothing Then CommentLength = Len(cell.Comment.Text) Else CommentLength = 0 End If
End Function

9. Kiểm tra độ dài dữ liệu trước khi nhập vào hệ thống (Data Validation)

Excel cung cấp Data Validation để ngăn người dùng nhập dữ liệu vượt quá độ dài cho phép.

1. Chọn ô hoặc dải ô cần áp dụng.
2. Data > Data Validation.
3. Tab Settings → Allow: Custom.
4. Công thức:

=LEN(A1)<=50

1. Tab Error Alert → Nhập thông báo lỗi (ví dụ: “Vui lòng nhập không quá 50 ký tự”).

Khi người dùng cố gắng nhập dữ liệu dài hơn 50 ký tự, Excel sẽ hiện thông báo và không cho nhập.

10. Đếm ký tự trong một chuỗi phân tách (đếm số từ, ký tự đặc biệt)

10.1. Đếm số từ

=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1)

• TRIM loại bỏ khoảng trắng đầu/cuối và giảm nhiều khoảng trắng liên tiếp thành một.

10.2. Đếm ký tự đặc biệt (ví dụ: dấu “#”)

=LEN(A2)-LEN(SUBSTITUTE(A2,"#",""))

10.3. Đếm ký tự ngoại trừ chữ và số (chỉ giữ lại ký tự đặc biệt)

=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"0",""),"1",""),"2","")) ... (tiếp tục cho a-z, A-Z)

Tuy nhiên, cách này không thực tế. Thay vào đó, Power Query hoặc VBA sẽ dễ dàng hơn:

= Text.Length(Text.Select(Column,"!0-9a-zA-Z"))

11. Tổng hợp các công thức hữu ích

12. Thực hành: Xây dựng bảng kiểm soát độ dài mô tả sản phẩm

Giả sử bạn có bảng sản phẩm với các cột:

Bước 1: Đặt công thức đếm độ dài

Ở ô D2:

=LEN(C2)

Kéo xuống để áp dụng cho toàn cột.

Bước 2: Xác định trạng thái (đúng/quá dài)

Ở ô E2 (giới hạn 200 ký tự):

=IF(D2<=200,"OK","Quá dài")

Bước 3: Đánh dấu màu tự động (Conditional Formatting)

1. Chọn cột E.
2. Home > Conditional Formatting > New Rule > Use a formula.
3. Công thức: =$E2="Quá dài" → Đặt màu nền đỏ.

Khi người dùng nhập mô tả, Excel sẽ tự động tính độ dài, hiển thị trạng thái và tô màu nếu vượt quá giới hạn.

13. Các lỗi thường gặp khi đếm ký tự và cách khắc phục

14. Tối ưu hiệu suất khi đếm ký tự trên bảng dữ liệu lớn

• Tránh dùng công thức mảng ({}) trên toàn cột (ví dụ: =LEN(A:A)) vì sẽ làm chậm.
• Sử dụng SUMPRODUCT thay vì ARRAYFORMULA trong các phiên bản Excel cũ.
• Chuyển sang Power Query nếu dữ liệu > 100.000 dòng; Power Query thực thi trên bộ nhớ và nhanh hơn.
• VBA: Khi cần tính toán một lần và lưu kết quả, viết macro để ghi giá trị vào cột tạm thời, thay vì để công thức tính liên tục.

Sub CountCharsBulk() Dim rng As Range, cell As Range Set rng = Range("A2:A50000") For Each cell In rng cell.Offset(0, 1).Value = Len(cell.Value) ' Ghi vào cột B Next cell
End Sub

Chạy macro này một lần, sau đó xóa công thức nếu không cần cập nhật liên tục.

15. Kết luận

Việc đếm số ký tự trong Excel không chỉ là một thao tác đơn giản mà còn là nền tảng cho nhiều quy trình kiểm soát chất lượng dữ liệu, bảo mật và tự động hoá. Từ hàm LEN cơ bản, đến các công thức kết hợp, SUMPRODUCT, Power Query, và VBA, bạn có thể đáp ứng hầu hết các yêu cầu thực tiễn:

• Kiểm tra độ dài mật khẩu hoặc mô tả sản phẩm.
• Giới hạn nhập dữ liệu bằng Data Validation hoặc macro.
• Phân tích văn bản như đếm số từ, ký tự đặc biệt, hoặc ký tự Unicode.
• Xử lý dữ liệu lớn một cách hiệu quả bằng Power Query hoặc macro.

Hãy áp dụng các kỹ thuật trên tùy thuộc vào mức độ phức tạp và khối lượng dữ liệu của bạn. Khi bạn thành thạo, việc quản lý độ dài ký tự sẽ trở nên nhanh chóng, chính xác và giúp nâng cao chất lượng dữ liệu trong mọi dự án Excel.

Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

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ẻ.