Một email có 2 dấu @ chắc chắn đã bị gõ sai, và một ô có 2 dấu phẩy nghĩa là có 3 mục. Đếm ký tự đặc biệt trong Excel giúp bạn kiểm tra dữ liệu như vậy, nhưng các dấu ? và * cần xử lý riêng vì COUNTIF coi chúng là ký tự đại diện.

Bài viết này tập trung vào ký tự đặc biệt. Nếu cần đếm chữ cái hoặc số từ, bạn có thể đọc bài đếm ký tự trong Excel. Bài không kèm file vì bạn chỉ cần tự gõ bảng. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, và màu tiêu đề trong ảnh chỉ để dễ nhìn.

Bạn cần gõ bảng này trước khi làm theo

Bạn gõ bảng dưới đây vào một sheet trống, bắt đầu từ ô A1. Ở ô A7, bạn gõ "Dòng 1", nhấn Alt + Enter, gõ "Dòng 2", nhấn Alt + Enter, gõ "Dòng 3", rồi nhấn Enter.

Nội dung

Kết quả

an.nguyen@gmail.com

binh@@abc.vn

Giá bao nhiêu?

SP*01

Chuột,Phím,Loa

Dòng 1, Dòng 2, Dòng 3 trên 3 hàng trong ô A7

Bảng gồm cột Nội dung có các ký tự đặc biệt và cột Kết quả
Ô A7 chứa 3 hàng chữ trong cùng một ô.

Trước mỗi cách, bạn xóa kết quả cũ ở cột B. Nếu Excel báo lỗi khi bạn gõ dấu phẩy ngăn cách tham số, máy của bạn đang dùng dấu chấm phẩy. Khi đó, bạn đổi các dấu phẩy ngăn cách tham số thành dấu chấm phẩy. Riêng dấu phẩy nằm trong dấu ngoặc kép là ký tự cần đếm, nên bạn giữ nguyên.

Cách 1: Hàm LEN và SUBSTITUTE đếm ký tự trong một ô

Hàm SUBSTITUTE xóa ký tự cần đếm, rồi hàm LEN so sánh độ dài trước và sau khi xóa. Phần chênh lệch chính là số ký tự đã xóa. Bạn gõ công thức sau vào B2:

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

Sau khi nhấn Enter, bạn chọn lại B2, đưa chuột vào chấm vuông nhỏ ở góc dưới bên phải ô cho đến khi con trỏ thành dấu cộng mảnh màu đen, rồi kéo xuống B7. Các dòng trả về 1, 2, 0, 0, 0 và 0. Dòng binh@@abc.vn trả về 2, nên email này đã bị gõ sai.

Công thức LEN và SUBSTITUTE đếm dấu @ trong từng ô
Dòng có 2 dấu @ dễ dàng được phát hiện bằng công thức này.

Hàm SUBSTITUTE hiểu ? và * đúng là ký tự bình thường. Ví dụ, =LEN(A4)-LEN(SUBSTITUTE(A4,"?","")) trả về 1.

Cách 2: Hàm SUMPRODUCT đếm tổng trong cả vùng

Bạn đưa cả vùng vào công thức và bọc bằng SUMPRODUCT. Bạn gõ công thức sau vào B2 và không kéo xuống:

=SUMPRODUCT(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"@","")))

Ô B2 trả về 3, là tổng số dấu @ trong cả vùng.

Công thức SUMPRODUCT đếm tổng 3 dấu @ trong cả vùng
Hàm SUMPRODUCT tính từng dòng rồi cộng các kết quả lại.

Cách 3: Hàm COUNTIF đếm số ô chứa ký tự

Cách này đếm số ô, không đếm số lần xuất hiện. Bạn gõ công thức sau vào B2:

=COUNTIF(A2:A7,"*@*")

Ô B2 trả về 2, vì có 2 ô chứa dấu @. Kết quả nhỏ hơn cách 2, vì ô binh@@abc.vn chỉ được tính là một ô.

Công thức COUNTIF đếm được 2 ô chứa dấu @
Hai dấu sao nghĩa là phía trước và phía sau dấu @ có thể có ký tự bất kỳ.

Cách 4: Dấu ngã giúp COUNTIF đếm dấu hỏi và dấu sao

Trong COUNTIF, dấu ? thay cho một ký tự bất kỳ, còn dấu * thay cho không hoặc nhiều ký tự bất kỳ. Vì vậy, công thức =COUNTIF(A2:A7,"*?*") trả về 6, tức là mọi ô có chữ. Để tìm đúng dấu hỏi, bạn đặt dấu ngã ~ ngay trước nó. Bạn gõ công thức sau vào B2:

=COUNTIF(A2:A7,"*~?*")

Ô B2 trả về 1, là ô "Giá bao nhiêu?".

Công thức COUNTIF với dấu ngã đếm đúng 1 ô chứa dấu hỏi
Dấu ngã báo cho Excel biết dấu hỏi là ký tự thật.

Bạn làm tương tự với dấu sao. Bạn gõ công thức sau vào B2:

=COUNTIF(A2:A7,"*~**")

Ô B2 trả về 1, là ô SP*01.

Công thức COUNTIF với dấu ngã đếm đúng 1 ô chứa dấu sao
Dấu sao đứng sau dấu ngã là ký tự thật, còn 2 dấu sao còn lại là ký tự đại diện.

Cách 5: Bạn đếm dấu phân cách để biết số mục trong ô

Khi không có dấu phẩy thừa ở đầu hoặc cuối, một ô có n dấu phẩy sẽ có n + 1 mục, nên bạn cộng thêm 1 vào kết quả đếm. Bạn gõ công thức sau vào B6:

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

Ô B6 trả về 3, ứng với Chuột, Phím và Loa.

Công thức đếm dấu phẩy cộng 1 cho biết ô có 3 mục
Hai dấu phẩy ngăn cách 3 mục trong cùng một ô.

Dấu xuống dòng cũng là một ký tự, và mã của nó là CHAR(10). Bạn gõ công thức sau vào B7 để đếm số hàng chữ trong ô A7:

=LEN(A7)-LEN(SUBSTITUTE(A7,CHAR(10),""))+1

Ô B7 trả về 3. Trong ảnh, ô B6 đã được xóa để bạn dễ nhìn kết quả mới.

Công thức dùng CHAR(10) đếm được 3 hàng chữ trong ô A7
Hai dấu xuống dòng chia ô A7 thành 3 hàng chữ.

Bạn thường gặp những lỗi nào khi đếm ký tự đặc biệt?

  • COUNTIF đếm thừa: Bạn có thể đã quên dấu ngã trước ? hoặc *. Công thức =COUNTIF(A2:A7,"*?*") trả về 6 thay vì 1.

  • Kết quả bằng 1 dù ô trống: Công thức đếm số mục cộng thêm 1, nên ô trống vẫn trả về 1. Bạn cần kiểm tra ô trống bằng công thức như =IF(A6="",0,LEN(A6)-LEN(SUBSTITUTE(A6,",",""))+1).

  • Kết quả thiếu vì chữ hoa, chữ thường: Hàm SUBSTITUTE có phân biệt chữ hoa, chữ thường. Ví dụ, công thức xóa "a" trong "Aa" chỉ đếm được 1. Công thức =LEN("Aa")-LEN(SUBSTITUTE(UPPER("Aa"),"A","")) đếm đủ 2.

  • Hàm SEARCH tìm sai dấu hỏi: Hàm SEARCH cũng coi ? là ký tự đại diện, nên =SEARCH("?",A2) trả về 1. Bạn dùng ~? hoặc đổi sang hàm FIND.

Người đọc thường hỏi gì về đếm ký tự đặc biệt trong Excel?

Tôi có thể đếm số ô chứa dấu hỏi bằng cách khác không?

Bạn có thể dùng hàm FIND, vì hàm này coi dấu hỏi là ký tự thường. Công thức =SUMPRODUCT(--ISNUMBER(FIND("?",A2:A7))) trả về 1.

Tôi có thể dùng cách đếm này với một đoạn nhiều ký tự không?

Bạn cần chia kết quả cho độ dài của đoạn đó. Ví dụ, bạn dùng =(LEN(A2)-LEN(SUBSTITUTE(A2,"an","")))/LEN("an") để đếm số lần đoạn "an" xuất hiện. Bài đếm ký tự trong Excel giải thích kỹ hơn phần này.

Bạn muốn dùng Excel thành thạo cho công việc văn phòng?

Đếm ký tự đặc biệt là cách kiểm tra dữ liệu rất nhanh, nhất là với danh sách email hoặc dữ liệu xuất từ phần mềm.

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.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.