Đếm số ký tự và đếm số chữ trong Excel dùng LEN và SUBSTITUTE

Khi làm việc với Excel, sẽ có rất nhiều bạn cần đếm xem giá trị trong một ô có bao nhiêu kí tự.

Cũng có khi bạn cần đếm số chữ (số từ) trong một ô. Các chữ, từ được cách nhau bởi khoảng trắng.

Ví dụ:

Cho cụm từ: Trung tâm tin học

• Cụm này có 17 kí tự (nhớ là luôn tính khoảng trắng)
• Cụm này gồm 4 từ (chữ)

Đề tài hôm nay mình sẽ giới thiệu các bạn cách lập công thức đếm số từ ngữ, số ký tự bằng các hàm trong Excel.

Đếm tất cả kí tự trong ô dùng hàm LEN

Ta sẽ dùng làm LEN

Ví dụ Ô A1 có giá trị là “Trung tâm tin học”

=LEN(A1)

Sẽ cho giá trị là 17. Tức chuỗi trong ô A1 có tổng cộng 17 kí tự

Đếm kí tự trong ô không tính khoảng trắng dùng hàm LEN và SUBSTITUTE

Ta dùng hàm LEN kết hợp hàm SUBSTITUTE

Hàm SUBSTITUTE có chức năng tìm và thay ký tự này bằng một kí tự khác trong chuỗi.

=SUBSTITUTE (A1, ” “, “”)

Ý nghĩa hàm trên là: tìm trong ô A1, những ký tự khoảng trắng ” ” và thay bằng rỗng “”

Sẽ cho kết quả là Trungtâmtinhọc

Như vậy ta đếm chuỗi kết quả trên thì đáp ứng yêu cầu.

Ta dùng công thức sau:

=LEN(SUBSTITUTE((A1),” “,””))

Sẽ cho giá trị là 14

Đếm số từ, chữ trong một ô dùng hàm LEN kết hợp

Các phân trên là đếm kí tự, phần này ta đếm số từ (số chữ). Các chữ là cụm kí tự cách nhau khoảng trắng.

Thay vì đếm bao nhiêu từ, ta sẽ đếm xem có bao nhiêu khoảng trắng ngăn cách giữa các từ rồi cộng thêm 1. Ta sẽ được số từ.

Ví dụ cụm từ: “Trần Triệu Phú” (Tên tôi!) có 2 khoảng trắng ngăn cách các từ, tức là cụm đó có 3 từ.

Muốn đếm số khoảng trắng ngăn cách trong chuỗi, ta dùng công thức kết hợp LEN và SUBSTITUTE như sau. Công thức này rất dễ hiểu nếu bạn hiểu 2 mục trước:

= LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))

Lấy giá trị này cộng thêm 1, ta sẽ được số chữ trong chuỗi đã cho, tức là:

= LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)) + 1

Sẽ có trường hợp do lỗi hoặc cố ý, các trong chuỗi có các khoảng trống thừa phía trước hoặc sau chuỗi, hoặc giữa các từ thay vì có 1 khoảng trắng thì có đến 2,3,4… khoảng trắng. Công thức trên sẽ bị sai.

Để giải quyết trường hợp này, ta dùng hàm TRIM là hàm có chức năng xóa các khoảng trắng thừa trong chuỗi.

Công thức đúng hơn sẽ là:

= LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””)) + 1

Bây giờ xảy ra một trường hợp nữa. Nếu giá trị của ô là rỗng hoặc một dãy các khoảng trắng tức nó có 0 từ. Khi đó biểu thức trên sẽ cho giá trị 1, SAI. Vì vậy ta kết hợp hàm IF  như sau:

=IF(LEN(TRIM(A1)=0, 0, LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1)

Tức là nếu A1 không có kí tự nào hoặc có một dãy các ký tự khoảng trắng thì hàm IF sẽ trả về 0, ngược lại có từ thì nó sẽ đếm từ theo công thức ở trên.

Các bạn còn có thể biến tấu công thức trên để đếm số lần xuất hiện của một từ cụ thể trong chuỗi cho trước.

Chúc các bạn thành công.

Trung Tâm Tin Học