Trong công việc văn phòng, bạn thường gặp những ô Excel chứa nhiều thông tin gộp chung một chỗ. Một ô ghi cả danh sách mã hàng của đơn hàng, một ô ghi lịch công tác cả tháng của nhân viên, một ô chứa họ tên đầy đủ. Khi sếp hỏi "đơn này có mấy mã nhóm A", "chị Lan đi Hà Nội mấy lần" hay "cột email có ô nào gõ sai không", việc đếm bằng mắt từng ô vừa chậm vừa dễ sót.

Bài viết này hướng dẫn 6 cách đếm ký tự trong Excel, đi từ đếm một ký tự trong một ô tới đếm trong cả vùng dữ liệu. Mỗi cách có công thức, giải thích vì sao công thức cho ra đúng kết quả và ảnh minh họa. Cuối bài có 2 ứng dụng thực tế: đếm số mã hàng trong một ô và kiểm tra email có đúng một dấu @. Mọi kết quả trong bài đã được kiểm tra lại bằng Excel, số liệu là ví dụ giả định.

Hàm LEN đếm ký tự như thế nào?

Hầu hết công thức trong bài đều dựa trên hàm LEN. Hàm LEN trả về số ký tự của một đoạn chữ, với cú pháp =LEN(văn bản). Đối số, tức giá trị bạn đặt trong cặp ngoặc đơn, thường là một ô, ví dụ =LEN(B2). Ví dụ, công thức =LEN("Nghề Excel") trả về 10, vì chữ "Nghề" có 4 ký tự, chữ "Excel" có 5 ký tự và dấu cách ở giữa cũng được tính là 1 ký tự.

LEN đếm tất cả ký tự trong ô, kể cả chữ cái, chữ số, dấu câu và dấu cách. Tuy vậy, LEN không tự đếm riêng được một ký tự cụ thể. Để biết chữ A xuất hiện mấy lần, bạn cần ghép LEN với hàm SUBSTITUTE, và đó là nội dung chính của bài này.

Hàm SUBSTITUTE thay một đoạn chữ bằng một đoạn chữ khác, với cú pháp =SUBSTITUTE(văn bản, chữ cũ, chữ mới):

  • Văn bản là ô chứa nội dung cần xử lý, ví dụ B2.

  • Chữ cũ là ký tự hoặc chuỗi bạn muốn thay, đặt trong dấu ngoặc kép, ví dụ "A".

  • Chữ mới là nội dung thay vào. Khi chữ mới là cặp ngoặc kép rỗng "", SUBSTITUTE xóa toàn bộ chữ cũ khỏi văn bản.

Ví dụ, ô B2 chứa "A101, B205, A102, C301". Công thức =SUBSTITUTE(B2,"A","") trả về "101, B205, 102, C301", tức nội dung cũ đã bị xóa hết chữ A.

Bảng tóm tắt giúp bạn chọn đúng cách đếm

Bạn cần đếm

Công thức mẫu

Cách

Số lần một ký tự xuất hiện trong ô

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

Cách 1

Một ký tự, không phân biệt hoa thường

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))

Cách 2

Số lần một chuỗi nhiều ký tự xuất hiện

=(LEN(B2)-LEN(SUBSTITUTE(B2,$G$2,"")))/LEN($G$2)

Cách 3

Số từ trong ô

=LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))+1

Cách 4

Số lần ký tự xuất hiện trong cả vùng

=SUMPRODUCT(LEN(B2:B6)-LEN(SUBSTITUTE(B2:B6,"A","")))

Cách 5

Số ô có chứa một ký tự hoặc chuỗi

=COUNTIF(B2:B6,"*C*")

Cách 6

Cách 1: Đếm một ký tự trong ô bằng LEN và SUBSTITUTE

Giả sử bạn làm ở bộ phận kho và có bảng theo dõi giao hàng. Cột A ghi số đơn hàng, cột B ghi các mã hàng đã giao trong đơn, mỗi mã cách nhau bởi dấu phẩy và một dấu cách. Mã bắt đầu bằng chữ A là hàng nhóm A. Bạn cần biết mỗi đơn có bao nhiêu mã nhóm A.

Công thức chung để đếm một ký tự trong ô là:

=LEN(ô)-LEN(SUBSTITUTE(ô,"ký tự",""))

Áp dụng vào bảng, bạn gõ công thức sau vào ô C2, rồi kéo nút vuông nhỏ ở góc dưới bên phải ô C2 xuống tới ô C6 để chép công thức cho các dòng còn lại:

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

Công thức LEN trừ LEN(SUBSTITUTE) đếm số lần chữ A xuất hiện trong mỗi ô mã hàng
Mỗi ô ở cột C cho biết đơn hàng có bao nhiêu mã nhóm A

Ô B2 chứa "A101, B205, A102, C301" nên kết quả ở C2 là 2, ứng với hai mã A101 và A102. Ô B4 chỉ có mã nhóm C nên kết quả là 0. Hai ô B3 và B6 có thêm mã viết thường (a108, a120), và công thức chưa đếm những mã này. Phần Cách 2 sẽ giải thích lý do và cách xử lý.

Vì sao công thức cho ra đúng số lần xuất hiện?

Công thức trông hơi lạ vì bạn không trực tiếp đếm chữ A, mà đo xem chuỗi ngắn đi bao nhiêu sau khi xóa chữ A. Để thấy rõ, bạn có thể tách công thức thành 4 cột phụ với ô B2:

  1. Bước 1: =LEN(B2) đếm độ dài ban đầu của ô B2. Chuỗi "A101, B205, A102, C301" có 4 mã, mỗi mã 4 ký tự, cộng 3 lần dấu phẩy và dấu cách, tổng cộng 22 ký tự.

  2. Bước 2: =SUBSTITUTE(B2,"A","") xóa hết chữ A, còn lại "101, B205, 102, C301".

  3. Bước 3: =LEN(SUBSTITUTE(B2,"A","")) đếm độ dài của chuỗi đã xóa chữ A, kết quả là 20.

  4. Bước 4: công thức lấy 22 trừ 20 ra 2. Mỗi chữ A bị xóa làm chuỗi ngắn đi đúng 1 ký tự, nên chuỗi ngắn đi bao nhiêu ký tự thì có bấy nhiêu chữ A.

Tách công thức đếm ký tự thành 4 cột phụ: độ dài, chuỗi sau khi xóa, độ dài mới, số chữ A
Độ dài giảm bao nhiêu thì có bấy nhiêu chữ A đã bị xóa

Khi đã hiểu 4 bước này, bạn sẽ thấy mọi công thức còn lại trong bài chỉ là biến thể của cùng một ý tưởng: xóa thứ cần đếm, rồi đo độ dài bị mất đi.

Mẹo: thay vì gõ cố định "A" trong công thức, bạn gõ ký tự cần đếm vào một ô riêng, ví dụ ô F1, rồi dùng công thức =LEN(B2)-LEN(SUBSTITUTE(B2,$F$1,"")). Dấu $ giữ cố định ô F1 khi bạn kéo công thức xuống. Khi muốn đếm ký tự khác, bạn chỉ cần sửa ô F1. Ví dụ, ô F1 chứa chữ C thì cột kết quả lần lượt là 1, 0, 2, 0, 1.

Cách 2: Đếm ký tự không phân biệt chữ hoa, chữ thường

Hàm SUBSTITUTE phân biệt chữ hoa và chữ thường. Khi bạn yêu cầu xóa "A", SUBSTITUTE chỉ xóa chữ A viết hoa và giữ nguyên chữ a viết thường. Vì vậy, ở Cách 1, ô B3 "B201, A105, a108" chỉ được đếm 1, dù thực tế đơn này có 2 mã nhóm A.

Trong dữ liệu gõ tay, việc lẫn chữ hoa và chữ thường xảy ra khá thường xuyên. Để đếm cả hai kiểu, bạn dùng hàm UPPER đổi toàn bộ nội dung ô sang chữ hoa trước khi xóa:

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))

Phần UPPER(B2) biến "B201, A105, a108" thành "B201, A105, A108", nên SUBSTITUTE xóa được cả 2 chữ A. Đổi chữ hoa không làm thay đổi số ký tự, nên phần LEN(B2) phía trước vẫn giữ nguyên. Lưu ý quan trọng là ký tự cần đếm trong công thức phải viết hoa, vì sau khi qua UPPER, trong chuỗi không còn chữ thường nào.

So sánh đếm chữ A có phân biệt và không phân biệt chữ hoa, chữ thường bằng hàm UPPER
Thêm UPPER giúp công thức đếm cả chữ a viết thường

Kết quả ở cột D lớn hơn cột C đúng 1 đơn vị ở dòng 3 và dòng 6, do hai dòng này có mã viết thường. Ngoài UPPER, bạn còn 2 cách viết khác cũng cho ra cùng kết quả:

  • Lồng hai hàm SUBSTITUTE: =LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2,"A",""),"a","")). Hàm SUBSTITUTE bên trong xóa chữ A hoa, hàm bên ngoài xóa tiếp chữ a thường.

  • Dùng ô tham chiếu: =LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),UPPER($F$1),"")). Công thức đổi cả nội dung ô lẫn ký tự ở ô F1 sang chữ hoa, nên bạn gõ "a" hay "A" vào F1 đều nhận về 2, 2, 0, 3, 3.

Bạn cũng có thể dùng hàm LOWER thay cho UPPER. Khi đó, mọi thứ được đổi sang chữ thường và ký tự cần đếm phải viết thường.

Cách 3: Đếm một chuỗi nhiều ký tự trong ô

Đôi khi bạn cần đếm cả một cụm chữ chứ không chỉ một ký tự, ví dụ đếm số lần chữ "Hà Nội" xuất hiện trong lịch công tác của nhân viên. Trong bảng ví dụ, cột B ghi các nơi nhân viên đã đi trong tháng, và ô G2 chứa chuỗi cần đếm là "Hà Nội".

Nếu bạn dùng nguyên công thức của Cách 1 =LEN(B2)-LEN(SUBSTITUTE(B2,$G$2,"")), ô C2 cho ra 12 chứ không phải 2. Lý do là mỗi lần xóa chữ "Hà Nội", chuỗi ngắn đi 6 ký tự chứ không phải 1 ký tự. Vì vậy, bạn cần chia số ký tự bị mất cho độ dài của chuỗi cần đếm. Công thức ở ô D2 là:

=(LEN(B2)-LEN(SUBSTITUTE(B2,$G$2,"")))/LEN($G$2)

Với ô B2 "Hà Nội, Đà Nẵng, Hà Nội, Huế", phần tử số ra 12 ký tự bị xóa, phần mẫu số LEN($G$2) bằng 6, và kết quả là 12 / 6 = 2 lần. Bạn cần để ý cặp ngoặc đơn bao quanh phép trừ. Nếu thiếu cặp ngoặc này, Excel sẽ thực hiện phép chia trước phép trừ và cho ra kết quả sai.

Đếm số lần chuỗi Hà Nội xuất hiện trong ô bằng cách chia cho độ dài chuỗi
Số ký tự bị xóa chia cho độ dài chuỗi ra số lần chuỗi xuất hiện

Dòng 5 có chữ "hà nội" viết thường, nên cột D chỉ đếm được 1 lần. Để đếm không phân biệt hoa thường, bạn dùng LOWER cho cả nội dung ô và chuỗi cần đếm, như công thức ở ô E2:

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($G$2),"")))/LEN($G$2)

Với cách này, dòng 5 cho ra 2 lần như mong đợi. Cách chia cho độ dài chuỗi cũng dùng được với mã hàng, từ khóa hay bất kỳ cụm chữ nào. Bạn chỉ cần lưu ý trường hợp chuỗi cần đếm nằm lọt bên trong một chuỗi dài hơn, được giải thích ở phần lỗi thường gặp bên dưới.

Cách 4: Đếm số từ trong ô

Excel không có hàm đếm từ riêng, nhưng bạn có thể đếm số từ dựa trên dấu cách. Trong một câu viết chuẩn, các từ cách nhau bởi đúng một dấu cách, nên số từ luôn bằng số dấu cách cộng 1. Ví dụ, "Nguyễn Văn An" có 2 dấu cách và 3 từ.

Số dấu cách được đếm giống hệt Cách 1, chỉ thay chữ A bằng dấu cách " ". Công thức cơ bản là:

=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1

Công thức này đúng khi dữ liệu sạch. Tuy nhiên, dữ liệu gõ tay hay bị thừa dấu cách ở đầu ô, ở cuối ô hoặc hai dấu cách liền nhau giữa các từ. Mỗi dấu cách thừa làm kết quả tăng thêm 1. Để khắc phục, bạn dùng hàm TRIM. Hàm TRIM xóa dấu cách ở đầu và cuối ô, đồng thời gộp nhiều dấu cách liền nhau thành một. Công thức đếm từ chuẩn hơn là:

=LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))+1

Phần LEN(TRIM(B2)) đo độ dài của nội dung đã dọn sạch dấu cách thừa. Phần LEN(SUBSTITUTE(B2," ","")) đo độ dài khi bỏ hết dấu cách, nên phần này không bị ảnh hưởng bởi dấu cách thừa. Hiệu của hai phần chính là số dấu cách "đúng chuẩn" giữa các từ.

Đếm số từ trong ô họ tên bằng số dấu cách cộng 1, kết hợp TRIM và IF để xử lý ô trống
TRIM sửa kết quả sai do dấu cách thừa, IF xử lý ô trống

Trong ảnh, ô B3 "Trần Thị Bích Ngọc" thừa một dấu cách giữa hai chữ "Thị" và "Bích" và một dấu cách ở cuối ô. Công thức không có TRIM đếm ra 6 từ, còn công thức có TRIM cho kết quả đúng 4 từ. Với ô B3, LEN(TRIM(B3)) bằng 18 và LEN(SUBSTITUTE(B3," ","")) bằng 15, nên kết quả là 18 - 15 + 1 = 4.

Ô trống ở dòng 6 vẫn cho ra 1, vì công thức luôn cộng thêm 1. Để ô trống được tính là 0 từ, bạn bọc công thức trong hàm IF như ở ô E2:

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

Hàm IF kiểm tra nếu ô chỉ toàn dấu cách hoặc để trống thì trả về 0, ngược lại mới đếm số từ. Bạn có thể xem thêm cách viết hàm IF trong bài Hàm IF nhiều điều kiện trong Excel.

Cách 5: Đếm ký tự trong cả một vùng bằng SUMPRODUCT

Bốn cách trên đếm trong từng ô. Khi cần một con số tổng cho cả vùng, ví dụ tổng số mã nhóm A trong mọi đơn hàng, bạn có thể tạo cột phụ rồi dùng SUM cộng lại. Nếu không muốn thêm cột phụ, bạn dùng hàm SUMPRODUCT để gói tất cả vào một ô.

Quay lại bảng đơn hàng ở Cách 1, công thức ở ô E3 đếm tổng số chữ A trong vùng B2:B6 là:

=SUMPRODUCT(LEN(B2:B6)-LEN(SUBSTITUTE(B2:B6,"A","")))

Công thức này giống hệt Cách 1, chỉ khác là ô B2 được thay bằng cả vùng B2:B6. Excel xử lý từng ô trong vùng như sau, trong đó cặp ngoặc nhọn { } biểu thị một danh sách nhiều giá trị:

  1. LEN(B2:B6) trả về độ dài của 5 ô: {22; 16; 10; 22; 28}.

  2. LEN(SUBSTITUTE(B2:B6,"A","")) trả về độ dài 5 ô sau khi xóa chữ A: {20; 15; 10; 19; 26}.

  3. Phép trừ cho ra số chữ A của từng ô: {2; 1; 0; 3; 2}.

  4. SUMPRODUCT cộng 5 con số này lại và trả về 8.

Hàm SUMPRODUCT đếm tổng số ký tự và tổng số chữ A trong vùng B2:B6
SUMPRODUCT cộng kết quả của từng ô trong vùng bằng một công thức

Kết quả 8 khớp với tổng của cột "Số mã nhóm A" ở Cách 1. Theo cùng cách đó, bạn có thêm 2 công thức hữu ích cho cả vùng:

  • Tổng số ký tự của cả vùng: =SUMPRODUCT(LEN(B2:B6)) trả về 98.

  • Tổng số chữ A, không phân biệt hoa thường: =SUMPRODUCT(LEN(B2:B6)-LEN(SUBSTITUTE(UPPER(B2:B6),"A",""))) trả về 10.

Bạn cũng có thể dùng SUM thay cho SUMPRODUCT, ví dụ =SUM(LEN(B2:B6)-LEN(SUBSTITUTE(B2:B6,"A",""))). Trên Excel 365 và Excel 2021, công thức SUM này chạy bình thường khi bạn nhấn Enter. Trên Excel 2019 trở về trước, bạn phải nhấn Ctrl + Shift + Enter để nhập dưới dạng công thức mảng. Nếu chỉ nhấn Enter, công thức sẽ báo lỗi #VALUE! hoặc trả về số sai. SUMPRODUCT không cần thao tác này trên mọi phiên bản, nên bạn dùng SUMPRODUCT khi file sẽ được gửi cho nhiều người.

Cách 6: Đếm số ô chứa một ký tự bằng COUNTIF

Năm cách trên trả lời câu hỏi "ký tự xuất hiện bao nhiêu lần". Trong nhiều báo cáo, câu hỏi lại là "có bao nhiêu ô chứa ký tự đó", ví dụ có bao nhiêu đơn hàng có mã nhóm C. Hai câu hỏi này cho ra hai con số khác nhau, và bạn dùng hàm COUNTIF với ký tự đại diện * cho câu hỏi thứ hai:

=COUNTIF(B2:B6,"*C*")

Dấu * trong điều kiện đại diện cho bất kỳ đoạn chữ nào, kể cả đoạn chữ rỗng. Điều kiện "*C*" nghĩa là ô có chữ C ở bất kỳ vị trí nào: đầu, giữa hay cuối ô. Nếu chữ cần tìm nằm ở ô F1, bạn viết điều kiện là "*"&F1&"*", trong đó dấu & nối dấu * với nội dung ô F1. Công thức đầy đủ là =COUNTIF(B2:B6,"*"&F1&"*").

Hàm COUNTIF với dấu * đếm số ô chứa chữ C, so sánh với số lần chữ C xuất hiện
COUNTIF đếm số ô chứa ký tự, không đếm số lần ký tự xuất hiện

Trong ảnh, chữ C xuất hiện 4 lần trong vùng B2:B6, nhưng chỉ có 3 ô chứa chữ C. Ô B4 "C302, C303" có 2 chữ C, nhưng COUNTIF chỉ tính ô này là 1 ô. Bạn chọn công thức theo câu hỏi cần trả lời: đếm số lần thì dùng SUMPRODUCT ở Cách 5, đếm số ô thì dùng COUNTIF.

Bạn cần nhớ thêm một điểm: COUNTIF không phân biệt chữ hoa, chữ thường, ngược với SUBSTITUTE. Vì vậy, công thức =COUNTIF(B2:B6,"*a*") đếm cả những ô chỉ có chữ A hoa và trả về 4. Nếu chỉ muốn đếm ô có chữ a viết thường, bạn dùng hàm FIND, vì FIND có phân biệt hoa thường:

=SUMPRODUCT(--ISNUMBER(FIND("a",B2:B6)))

Hàm FIND tìm vị trí của chữ a trong từng ô. Ô có chữ a thì FIND trả về một con số, ô không có thì FIND báo lỗi. ISNUMBER đổi kết quả đó thành TRUE hoặc FALSE, hai dấu trừ đổi TRUE thành 1 và FALSE thành 0, rồi SUMPRODUCT cộng lại. Kết quả là 2, ứng với ô B3 và ô B6.

Nếu bạn cần đếm số lần một giá trị xuất hiện trong cả cột, ví dụ mỗi khách hàng mua bao nhiêu đơn, bạn có thể đọc thêm bài 6 cách đếm tần suất xuất hiện trong Excel.

Hai ứng dụng thực tế bạn có thể dùng ngay

Ứng dụng 1: Đếm số mã hàng trong một ô

Khi các mã trong một ô được ngăn cách bởi dấu phẩy, số mã luôn bằng số dấu phẩy cộng 1. Với bảng đơn hàng ở trên, bạn gõ vào ô C2 công thức:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

Ô B2 có 3 dấu phẩy nên đơn DH001 có 4 mã hàng. Khi kéo công thức xuống, bạn nhận được 4, 3, 2, 4, 5, và tổng cả 5 đơn là 18 mã. Bạn cũng có thể lấy ngay con số 18 trong một ô bằng =SUMPRODUCT(LEN(B2:B6)-LEN(SUBSTITUTE(B2:B6,",",""))+1). Giống cách đếm từ, nếu cột có ô trống, bạn bọc công thức trong IF để ô trống ra 0: =IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1).

Ứng dụng 2: Kiểm tra email có đúng một dấu @

Một địa chỉ email hợp lệ phải có đúng một dấu @. Khi nhận danh sách email gõ tay, bạn dùng công thức đếm ký tự để lọc nhanh các ô cần xem lại. Ô C2 đếm số dấu @:

=LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))

Ô D2 đưa ra kết luận dựa trên kết quả đó:

=IF(C2=1,"Đạt","Kiểm tra lại")

Ứng dụng đếm ký tự: đếm số mã hàng trong một ô và kiểm tra email có đúng một dấu @
Hai ứng dụng thực tế của công thức đếm ký tự

Bảng email cho thấy 3 trường hợp cần kiểm tra lại. Ô B3 gõ thừa một dấu @, ô B4 thiếu dấu @, còn ô B5 chứa hai email trong cùng một ô. Nếu không muốn dùng cột phụ C, bạn gộp hai bước vào một công thức =IF(LEN(B2)-LEN(SUBSTITUTE(B2,"@",""))=1,"Đạt","Kiểm tra lại"). Sau đó, công thức =COUNTIF(D2:D6,"Kiểm tra lại") cho biết có 3 email cần xem lại.

Lưu ý rằng đúng một dấu @ chỉ là điều kiện tối thiểu. Một email có một dấu @ vẫn có thể sai ở phần khác, ví dụ gõ nhầm tên miền. Công thức này giúp bạn lọc nhanh các lỗi rõ ràng, còn những ô "Đạt" vẫn cần được kiểm tra theo cách khác nếu dữ liệu quan trọng.

Bạn hay gặp những lỗi nào khi đếm ký tự?

Bạn thấy

Nguyên nhân

Cách sửa

Kết quả bằng 0 dù ô có ký tự cần đếm

SUBSTITUTE phân biệt hoa thường, ký tự trong ô viết khác kiểu với ký tự trong công thức

Dùng UPPER hoặc LOWER như Cách 2

Đếm chữ tiếng Việt ra 0 dù nhìn thấy rõ

Văn bản dán từ nguồn khác dùng bảng mã Unicode tổ hợp, nên "Hà Nội" dài 9 ký tự thay vì 6 và không khớp với chữ bạn gõ

Gõ lại hoặc dùng công cụ chuyển mã của UniKey để đưa về bảng mã Unicode dựng sẵn

Đếm chuỗi ra số rất lớn, ví dụ 12 thay vì 2

Quên chia cho độ dài chuỗi cần đếm

Chia cho LEN(chuỗi) như Cách 3

Đếm chuỗi ra số lẻ như 25,33

Thiếu cặp ngoặc bao quanh phép trừ, nên Excel chia trước rồi mới trừ

Viết đủ (LEN(...)-LEN(...))/LEN(...)

Đếm "A11" ra 3 dù không có mã A11 nào

Chuỗi "A11" nằm bên trong các mã A110, A111, A112

Trên Excel 365 hoặc Excel 2024, dùng =SUM(--(TEXTSPLIT(B5,", ")="A11")) để so khớp nguyên mã

Ô trống vẫn ra 1 từ hoặc 1 mã hàng

Công thức luôn cộng thêm 1

Bọc trong IF(TRIM(B2)="",0,...)

SUM(LEN(...)) báo #VALUE! hoặc ra số sai

Excel 2019 trở về trước cần nhập công thức mảng

Nhấn Ctrl + Shift + Enter hoặc đổi sang SUMPRODUCT

COUNTIF đếm cả ô có chữ hoa lẫn chữ thường

COUNTIF không phân biệt hoa thường

Dùng SUMPRODUCT kết hợp ISNUMBER và FIND

Lỗi đếm "A11" xảy ra vì cách xóa rồi đo độ dài chỉ tìm chuỗi ký tự, không biết đâu là ranh giới giữa các mã. Ô B5 "A110, A111, A112, B210" chứa chuỗi "A11" ở cả ba mã đầu, nên công thức =(LEN(B5)-LEN(SUBSTITUTE(B5,"A11","")))/LEN("A11") trả về 3. Hàm TEXTSPLIT tách ô thành từng mã riêng theo dấu phẩy và dấu cách, sau đó phép so sánh chỉ đếm những mã trùng khớp hoàn toàn với "A11", nên kết quả đúng là 0. TEXTSPLIT chỉ có trên Excel 365 và Excel 2024. Trên phiên bản cũ hơn, bạn nên đặt mã hàng có độ dài cố định để không mã nào nằm lọt trong mã khác.

Câu hỏi thường gặp về đếm ký tự trong Excel

Làm sao đếm số ký tự trong ô mà không tính dấu cách?

Bạn xóa hết dấu cách rồi mới đếm: =LEN(SUBSTITUTE(B2," ","")). Với ô chứa "Nguyễn Văn An", công thức trả về 11 thay vì 13.

Làm sao đếm cùng lúc hai ký tự khác nhau, ví dụ cả chữ A và chữ B?

Bạn đếm từng ký tự rồi cộng lại: =(LEN(B2)-LEN(SUBSTITUTE(B2,"A","")))+(LEN(B2)-LEN(SUBSTITUTE(B2,"B",""))). Với ô B2 "A101, B205, A102, C301", công thức trả về 3, gồm 2 chữ A và 1 chữ B.

Làm sao đếm số dòng trong một ô có xuống dòng bằng Alt + Enter?

Mỗi lần nhấn Alt + Enter, Excel chèn một ký tự xuống dòng, và ký tự này được viết trong công thức là CHAR(10). Bạn đếm số ký tự xuống dòng rồi cộng 1: =LEN(B2)-LEN(SUBSTITUTE(B2,CHAR(10),""))+1. Ô có 3 dòng chữ sẽ cho kết quả 3.

COUNTIF và công thức LEN, SUBSTITUTE khác nhau thế nào?

COUNTIF đếm số ô chứa ký tự, còn LEN kết hợp SUBSTITUTE đếm số lần ký tự xuất hiện. Một ô chứa chữ C hai lần được COUNTIF tính là 1, còn công thức LEN, SUBSTITUTE tính là 2.

Bạn muốn dùng thành thạo các hàm xử lý chữ trong Excel?

Đếm ký tự chỉ là một phần trong nhóm hàm xử lý chữ. Khi đã quen với LEN, SUBSTITUTE, TRIM, UPPER, bạn sẽ dễ dàng học tiếp các hàm tách chữ như LEFT, RIGHT, MID để làm sạch dữ liệu khách hàng, chuẩn hóa mã hàng hay tách họ tên. Bạn có thể ôn lại các hàm nền tảng trong bài 16 hàm Excel cơ bản dân văn phòng nên biết và xem thứ tự học hợp lý trong bài Lộ trình học Excel từ cơ bản đến nâng cao.

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.