Công thức Excel: Đếm tổng số từ trong một ô -

Mục lục

Công thức chung

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

Tóm lược

Để đếm tổng số từ trong một ô, bạn có thể sử dụng công thức dựa trên hàm LEN và SUBSTITUTE, với sự trợ giúp từ hàm TRIM. Trong ví dụ được hiển thị, công thức trong ô C5, được sao chép xuống, là:

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

Công thức trả về số lượng từ trong ô B5.

Giải trình

Excel không có chức năng chuyên dụng để đếm các từ trong một ô. Tuy nhiên, với một chút khéo léo, bạn có thể tạo một công thức như vậy bằng cách sử dụng hàm SUBSTITUTE và LEN, với sự trợ giúp từ TRIM, như trong ví dụ. Ở cấp độ cao, công thức này sử dụng hàm LEN để đếm số ký tự trong ô, có và không có dấu cách, sau đó sử dụng sự khác biệt để tính số từ. Điều này hoạt động, vì số lượng từ bằng số khoảng trắng + 1, miễn là có một khoảng trắng giữa mỗi từ.

Phần đầu tiên của công thức đếm các ký tự trong ô B5, sau khi loại bỏ khoảng trắng thừa:

=LEN(TRIM(B5)) // normalize space, count characters

Bên trong LEN, hàm TRIM trước tiên sẽ loại bỏ bất kỳ khoảng trắng thừa nào giữa các từ hoặc ở đầu hoặc cuối văn bản. Điều này rất quan trọng, vì bất kỳ khoảng trắng thừa nào sẽ không đếm được từ. Trong trường hợp này, không có ký tự khoảng trắng thừa, vì vậy TRIM trả về văn bản gốc trực tiếp cho hàm LEN, hàm này trả về 30:

LEN("All Quiet on the Western Front") // returns 30

Tại thời điểm này, chúng tôi có:

=30-LEN(SUBSTITUTE(B5," ",""))+1

Tiếp theo, chúng tôi sử dụng hàm SUBSTITUTE để xóa tất cả các ký tự khoảng trắng khỏi văn bản:

SUBSTITUTE(B5," ","") // strip all space

Chú ý SUBSTITUTE được cấu hình để tìm kiếm một ký tự khoảng trắng ("") và thay thế bằng một chuỗi rỗng (""). Theo mặc định, SUBSTITUTE sẽ thay thế tất cả các khoảng trắng. Kết quả được gửi trực tiếp đến hàm LEN, hàm này trả về số lượng:

LEN("AllQuietontheWesternFront") // returns 25

LEN trả về 25, số ký tự còn lại sau khi đã xóa hết khoảng trắng. Bây giờ chúng ta có thể đơn giản hóa công thức thành:

=30-25+1 // returns 6

trả về 6 là kết quả cuối cùng, số từ trong ô B5.

Xử lý các ô trống

Công thức trong ví dụ sẽ trả về 1 ngay cả khi một ô trống hoặc chỉ chứa khoảng trắng. Điều này xảy ra bởi vì chúng ta đang thêm 1 vô điều kiện, sau khi đếm các ký tự khoảng trắng giữa các từ. Để đề phòng vấn đề này, bạn có thể điều chỉnh công thức như hình dưới đây:

=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+(LEN(TRIM(B5))>0)

Lưu ý rằng chúng tôi đã thay thế 1 bằng biểu thức này:

LEN(TRIM(B5))>0

Mã này đầu tiên cắt B5, sau đó kiểm tra độ dài. Nếu B5 chứa văn bản, LEN trả về một số dương và biểu thức trả về TRUE. Nếu B5 trống hoặc chỉ chứa khoảng trắng, TRIM trả về một chuỗi trống ("") cho LEN. Trong trường hợp đó, LEN trả về không (0) và biểu thức trả về FALSE. Bí quyết là TRUE và FALSE lần lượt đánh giá bằng 1 và 0 khi tham gia vào bất kỳ phép toán nào. Kết quả là, biểu thức chỉ thêm 1 khi có văn bản trong B5. Nếu không, nó thêm không (0). Logic này cũng có thể được viết bằng câu lệnh hàm IF như sau: