Nếu như bạn đang tìm kiếm một công thức để đếm số ký tự trong một ô khi thao tác với Excel thì hàm LEN sẽ là một lựa chọn không thể bỏ qua. Với hàm này, bạn có thể linh động đếm số kí tự trong chuỗi khi có tính hay không tính khoảng trống giữa các từ.
HÀM LEN TRONG EXCEL
Hàm LEN có thể đếm được tất cả ký tự trong một ô, rồi sau đó trả về chiều dài chuỗi.
Hàm này chỉ có một câu lệnh đơn giản: =LEN(text)
Text ở đây chính là chuỗi ký tự mà bạn muốn đếm số ký tự.
Các minh họa dưới đây sẽ giúp bạn hiểu rõ hơn công dụng của hàm LEN trong Excel.
=LEN(123) – được trả về 3, vì 3 chữ số được nhập vào câu lệnh text.
=LEN(“good”) – được trả về 4, vì từ “good” ở đây có 4 chữ cái.
Cũng giống như bất cứ công thức Excel khác, hàm LEN bắt buộc phải đặt chuỗi ký tự bên trong dấu ngoặc kép, và tất nhiên dấu ngoặc kép này sẽ không được đếm. Khi dùng công thức LEN, bạn có thể phải nhập tham chiếu ô thay vì chữ số hoặc chuỗi ký tự, để thực hiện đếm số ký tự trong một ô cụ thể hoặc một dải ô.
Ví dụ như, để tính được chiều dài của chuỗi ký tự trong ô A1, bạn sẽ sử dụng công thức như sau: =LEN(A1)
CÁCH DÙNG HÀM LEN TRONG EXCEL – MẪU CÔNG THỨC
Như những diễn giải bên trên, bạn có thể nghĩ việc sử dụng hàm LEN thật quá đơn giản. Tuy vậy, nếu bạn biết cách áp dụng các mẹo dưới đây bạn hoàn toàn có thể chủ động trong mọi tình huống gặp phải đấy!
CÁCH ĐẾM TẤT CẢ KÝ TỰ TRONG MỘT Ô (BAO GỒM KHOẢNG TRỐNG)
Ví dụ, để có thể tính chiều dài của ô A2, bạn dùng công thức như sau: =LEN(A2) Như hình minh họa bên dưới, công thức LEN đếm được 36 ký tự, trong đó bao gồm 29 chữ cái, 1 chữ số, và 6 khoảng trống.
ĐẾM SỐ KÝ TỰ TRONG NHIỀU Ô
Để tiến hành đếm số ký tự trong nhiều ô, bạn hãy chọn ô có công thức LEN sau đó thực hiện sao chép sang ô khác, ví dụ bằng cách kéo ô vuông nằm dưới góc bên phải . Sau khi đã thực hiện sao chép công thức, hàm LEN lúc này sẽ trả về số ký tự đếm được trong mỗi ô.
Bạn hãy luôn lưu ý rằng hàm LEN sẽ đếm tất cả mọi thứ một cách tuyệt đối gồm chữ cái, chữ số, khoảng trống, dấu ngoặc kép, dấu phẩy, dấu ngoặc đơn,…
Lưu ý. Khi thực hiện sao chép một công thức xuống các cột khác, bạn phải chắc chắn rằng đã dùng tham chiếu ô tương ứng, chẳng hạn như LEN(A1) hoặc là một tham chiếu hỗn hợp như LEN($A1) chỉ sử dụng cố địng cho cột, và lúc này công thức LEN của bạn sẽ được điều chỉnh một cách thích hợp cho vị trí mới.
ĐẾM TẤT CẢ KÝ TỰ TRONG NHIỀU Ô
Cách rõ ràng nhất để tiến hành đếm tất cả các ký tự trong vài ô là thực hiện thêm vài hàm LEN nữa, ví dụ như:
=LEN(A2)+LEN(A3)+LEN(A4) Hoặc là sử dụng hàm SUM để tiến hành tính tổng số ký tự được trả về bởi hàm LEN:
=SUM(LEN(A2),LEN(A3),LEN(A4))
Bạn có thể sử dụng một trong hai cách, công thức đều đếm số ký tự trong mỗi ô xác định rồi trả về tổng chiều dài chuỗi: Tuy nhiên, để thực hiện đếm số thứ tự trong một dãy dài lên đến 100 hay 1000 ô, bạn nên sử dụng hàm SUM và hàm LEN trong công thức mảng, hãy theo dõi kĩ hơn ở ví dụ tiếp theo đây.
CÁCH ĐẾM SỐ KÝ TỰ KHÔNG TÍNH KHOẢNG TRỐNG THỤT ĐẦU DÒNG VÀ THỤT CUỐI DÒNG
Khi làm việc với bảng tính lớn thì vấn đề phổ biến sẽ là khoảng cách đầu dòng và cuối dòng, ví dụ như: khoảng trống ở đầu hay là ở cuối ký tự. Nếu bạn đang nghi ngờ có vài khoảng trống vô hình trong các ô thì hàm LEN sẽ giúp ích rất nhiều trong trường hợp này.
Để thực hiện tính được chiều dài chuỗi mà không phải đếm khoảng trống đầu dòng và cuối dòng, hãy thao tác lồng ghép hàm TRIM vào hàm LEN trong Excel: =LEN(TRIM(A2))
CÁCH ĐẾM SỐ KÝ TỰ TRONG MỘT Ô MÀ KHÔNG TÍNH CÁC KHOẢNG TRỐNG
Nếu mục đích của bạn ở đây là đếm số ký tự mà không tính những khoảng trống cho dù đó là khoảng trống đầu dòng, cuối dòng hoặc là giữa các từ, thì bạn sẽ cần sử dụng một công thức phức tạp hơn:
=LEN(SUBSTITUTE(A2,” “,””))
Hàm SUBSTITUTE lúc này sẽ thay thế ký tự này với ký tự khác. Ở công thức trên, bạn có thể thay thế khoảng trống (” “) bằng một ký tự rỗng, ví dụ như một chuỗi ký tự rỗng (“”).
Và vì bạn thực hiện lồng ghép hàm SUBSTITUTE vào hàm LEN nên phép thế lúc này thật sự không được thực hiện trong các ô, mà nó chỉ có tác dụng hướng dẫn công thức LEN tính chiều dài chuỗi mà không tính các khoảng trống.
CÁCH ĐẾM SỐ KÝ TỰ TRƯỚC HAY SAU MỘT KÝ TỰ XÁC ĐỊNH
Thông thường, bạn sẽ cần phải biết chiều dài của một phần nhất định của chuỗi ký tự, thay cho việc đếm tổng số ký tự trong một ô.
Ví dụ như, bạn đang có một danh sách mã hàng hóa như sau: Và toàn bộ mã hàng hóa hợp lệ đều có chính xác là 5 ký tự trong nhóm đầu tiên.
Vậy làm thế nào để bạn có thể kiểm tra được mã hàng hóa nào đang không hợp lệ? Đó chính là sử dụng việc đếm số ký tự trước dấu gạch ngang đầu tiên.
Công thức chiều dài Excel lúc này sẽ có dạng như sau: =LEN(LEFT($A2, SEARCH(“-“, $A2) -1))
Và bây giờ, hãy thực hiện chia nhỏ công thức ra để bạn có thể hiểu quy luật của nó:
Bạn dùng hàm SEARCH để tiến hành trả về vị trí của dấu gạch ngang đầu tiên (“-“) trong ô A2: SEARCH(“-“, $A2)
Sau đó, bạn dùng hàm LEFT để thực hiện trả về các ký tự phía bên trái của chuỗi ký tự và lấy kết quả trừ 1 bởi vì bạn không muốn tính luôn dấu gạch ngang: LEFT($A2,SEARCH(“-“, $A2) -1))
Cuối cùng, bạn có hàm LEN để trả về chiều dài của chuỗi đó. Lúc này, có lẽ bạn sẽ muốn thực hiện thêm một bước nữa, là đánh dấu mã hàng hóa không hợp lệ bằng cách tạo ra một quy tắc định dạng điều kiện đơn giản có công thức =$B2<>5:
Hoặc là, bạn có thể xác định được mã hàng hóa không hợp lệ bằng việc lồng ghép công thức LEN ở trên vào hàm IF: =IF(LEN(LEFT($A2, SEARCH(“-“, $A2) -1))<>5, “Invalid”, “”)
Như được giả thích trong ảnh dưới đây, công thức xác định mã hàng hóa không hợp lệ một cách hoàn hảo dựa trên chiều dài chuỗi, và bạn sẽ không cần một cột đếm ký tự riêng biệt: Tương tự như vậy, bạn có thể dùng hàm LEN trong Excel để thực hiện đếm số ký tự sau một ký tự cụ thể.
Ví dụ như, trong một danh sách tên, có thể bạn sẽ muốn biết họ của một người có bao nhiêu ký tự. Cứ áp dụng công thức LEN để giải quyết việc này: =LEN(RIGHT(A2, LEN(A2) -SEARCH(” “,A2)))
Đầu tiên, bạn xác định vị trí của khoảng trống (” “) trong chuỗi ký tự bằng cách dùng hàm SEARCH: SEARCH(“-“,A2)))
Tiếp đó, bạn thực hiện đếm xem có bao nhiêu ký tự nằm phía sau khoảng trống. Về việc này, bạn chỉ cần lấy tổng chiều dài chuỗi trừ đi vị trí khoảng trống: LEN(A2) -SEARCH(“_”, A2)))
Tiếp đến, bạn dùng hàm RIGHT để trả về tất cả ký tự phía sau khoảng trống. Và cuối cùng, bạn sử dụng hàm LEN để thực hiện tính chiều dài chuỗi được trả về bởi hàm RIGHT.
Lưu ý rằng, để công thức có thể hoạt động một cách chính xác, mỗi ô nên có chỉ một khoảng trống, ví dụ như chỉ có tên và họ, không có tên lót, danh hiệu hay là hậu tố. Hi vọng những kiến thức chia sẻ trong bài viết sẽ giúp ích được nhiều cho các bạn!