Cách dùng hàm LEN trong Excel để đếm số ký tự trong một ô

Bạn đang tìm kiếm công thức Excel để đếm số ký tự trong một ô phải không? Bài hướng dẫn dưới đây sẽ dạy bạn cách sử dụng hàm LEN trong excel để đếm số ký tự, cho dù bạn có tính hay không tính khoảng trống giữa các từ.

Đối với tất cả các hàm Excel, hàm LEN còn được coi là hàm dễ và đơn giản nhất. Tên hàm Excel cũng rất dễ ghi nhớ, không có gì ngoài ba ký tự đầu của từ “length”. Và đó là tác dụng thật sự của hàm LEN trong Excel – trả về chiều dài trong chuỗi ký tự, hay chiều dài của một ô.

Nói theo cách khác thì các bạn sử dụng hàm LEN trong hàm Excel để đếm tất cả ký tự trong một ô, trong đó chữ cái, chữ số, ký tự đặc biệt, và tất cả khoảng trống. Trong bài hướng dẫn dưới đây, chúng ta sẽ xem xét nhanh cú pháp trước sau đó sẽ tìm hiểu kỹ một vài mẫu công thức để đếm số ký tự có trong bảng tính hàm Excel.

I. Hàm LEN trong Excel là gì?

Hướng dẫn sử dụng hàm Len trong Excel

Hàm LEN trong Excel là đếm tất cả ký tự trong một ô sau đó trả về chiều dài chuỗi. Nó chỉ có một câu lệnh và câu lệnh này chắc chắn phải có rồi:=LEN(text)

Text chính là một chuỗi ký tự nơi mà bạn muốn đếm số ký tự. Chẳng có gì là dễ dàng hơn thế, đúng không nào? Bạn sẽ tìm thấy một vài công thức đơn giản bên dưới để nhằm giúp bạn hiểu sơ qua công dụng của hàm LEN trong Excel =LEN(123) – sẽ trả về 3, bởi vì 3 chữ số được nhập vào câu lệnh text.

=LEN(“good”) – là trả về 4, bởi vì từ good sẽ có 4 chữ cái. Giống như là bất cứ công thức các hàm Excel nào khác, hàm LEN trong Excel bắt buộc bạn phải đặt chuỗi ký tự ở bên trong dấu ngoặc kép, và dấu ngoặc kép này sẽ không được đếm.

Khi bạn sử dụng công thức hàm LEN thì bạn có thể cần phải nhập thêm tham chiếu ô thay vì các chữ số hay chuỗi ký tự, để đếm số ký tự trong một ô cụ thể hay một dải ô.

Chẳng hạn, để tính được chiều dài của chuỗi ký tự trong ô A1, bạn sẽ dùng công thức này: =LEN(A1)

II. Cách dùng hàm LEN trong Excel - công thức mẫu

Nhìn qua, hàm LEN trông sẽ đơn giản đến nỗi chẳng cần phải giải thích gì thêm nữa. Nhưng, có một số mẹo hữu ích để có thể giúp bạn điều chỉnh công thức hàm LEN trong Excel theo mong muốn của mình.

III. Cách đếm tất cả ký tự trong cùng một ô (bao gồm khoảng trống)

Như đã nói trước kia thì hàm LEN trong Excel đếm tất cả ký tự trong một ô cụ thể một cách tuyệt đối, trong đó gồm tất cả khoảng trống – thụt đầu dòng, thụt cuối dòng, và khoảng trống ở giữa các từ.

Ví dụ, để tính chiều dài của ô A2 thì bạn cần sử dụng công thức này:=LEN(A2). Như được diễn giải thì công thức hàm LEN đếm được 36 ký tự, bao gồm 29 chữ cái, 1 chữ số, và có 6 khoảng trống.

IV. Đếm số ký tự trong nhiều ô

Để có thể đếm số ký tự trong nhiều ô thì bạn hãy chọn ô có công thức hàm LEN trong Excel rồi sau đó 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 .

Ngay khi sao chép công thức, hàm LEN trong Excel sẽ trả về số ký tự đếm được ở mỗi ô. Và một lần nữa, bạn hãy lưu ý rằng hàm LEN trong Excel đếm tất cả mọi thứ một cách tuyệt đối trong đó có bao gồm chữ cái, chữ số, khoảng trống, dấu phẩy, dấu ngoặc kép, dấu ngoặc đơn, và còn nhiều ký tự hơn nữa.

Lưu ý. Khi sao chép một công thức xuống các cột khác thì hãy chắc rằng bạn sử dụng tham chiếu ô tương đối ví dụ như là LEN(A1), hoặc một tham chiếu hỗn hợp như LEN($A1) chỉ dùng cố định cho cột, nên công thức hàm LEN trong Excel của bạn sẽ điều chỉnh một cách thích hợp với vị trí mới.

V. Đếm tất cả ký tự trong nhiều ô

Cách rõ ràng nhất để đếm tất cả ký tự trong vài ô là thêm một số hàm LEN trong Excel nữa, ví dụ: =LEN(A2)+LEN(A3)+LEN(A4) hoặc, hãy sử dụng hàm SUM để có thể tính tổng số ký tự được trả về bởi hàm LEN trong Excel: =SUM(LEN(A2),LEN(A3),LEN(A4))

Cách nào cũng được đối với 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: Cách này chắc chắn dễ hiểu và cũng dễ dàng sử dụng, tuy nhiên nó không phải là cách hiệu quả duy nhất để đếm số ký tự trong một dải bao gồm có, giả sử, 100 hoặc 1000 ô. Đối với trường hợp này, bạn nên sử dụng hàm SUM và hàm LEN trong Excel với công thức mảng.

VI. Cách đếm số ký tự không tính khoảng trống thụt đầu và cuối dòng

Cách dùng hàm LEN trong Excel để đếm số ký tự trong một ô

Khi thực hiện thao tác với bảng tính lớn thì vấn đề phổ biến nhất đó là khoảng cách giữa đầu dòng và cuối dòng, ví dụ như là: khoảng trống ở đầu hoặc ở cuối ký tự. Có lẽ bạn hầu như không thấy chúng xuất hiện trên bảng tính, tuy nhiên sau khi bạn gặp chúng một vài lần, thì bạn sẽ dễ nhận thấy chúng hơn.

Nếu bạn đang nghi ngờ có vài khoảng trống vô hình trong các ô thì hàm LEN trong Excel sẽ giúp ích cho bạn rất nhiều. Hy vọng bạn còn nhớ và nó đếm tất cả khoảng trống trong phép đếm ký tự: Để tính được chiều dài trong chuỗi mà không đếm khoảng trống đầu dòng và cuối dòng bạn hãy lồng ghép hàm TRIM vào hàm LEN trong Excel: =LEN(TRIM(A2))

VII. 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 đó là đếm số ký tự mà không tính các khoảng trống cho dù đó là khoảng trống đầu dòng, cuối dòng hay giữa các từ, thì bạn sẽ cần một công thức phức tạp của hàm Len trong Excel đó là: =LEN(SUBSTITUTE(A2,” “,””))

Có lẽ bạn đã biết, hàm SUBSTITUTE sẽ thay thế ký tự này đối với ký tự khác. Trong công thức trên, bạn thay thế các khoảng trống (” “) bằng một ký tự rỗng, ví dụ một chuỗi ký tự rỗng (“”). Và bởi vì bạn lồng ghép hàm SUBSTITUTE vào hàm LEN trong Excel nên phép thay thế thật sự không được thực hiện trong các ô, mà nó chỉ hướng dẫn công thức hàm LEN trong Excel sẽ tính chiều dài chuỗi mà không tính các khoảng trống.

VIII. Cách đếm số ký tự trước hay sau một ký tự xác định

Thông thường thì bạn cần phải biết chiều dài của một phần nhất định trong chuỗi ký tự, thay vì đếm tổng số ký tự trong một ô.

Giả sử, bạn có một danh sách mã hàng hóa như dưới đây: Và toàn bộ mã hàng hóa hợp lệ đều có chính xác 5 ký tự ở trong nhóm đầu tiên. Vậy làm thế nào để bạn chỉ ra được mã hàng hóa nào là không hợp lệ? Vậy thì, bằng cách đếm số ký tự trước dấu gạch ngang đầu tiên. Công thức chiều dài các hàm Excel có dạng như sau: = LEN(LEFT($A2, SEARCH(“-“, $A2) -1)). Và bây giờ, bạn hãy chia nhỏ các công thức ra để bạn có thể hiểu quy luật của nó:

Bạn có thể sử dụng hàm SEARCH để trả về vị trí của dấu gạch ngang đầu tiên (“-“) trong ô A2: SEARCH(“-“, $A2). Sau đó, bạn sử dụng hàm LEFT để trả về các ký tự phía bên trái của chuỗi ký tự, rồi 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)). Và cuối cùng, bạn có hàm LEN trong Excel để trả về chiều dài của chuỗi đó.

Ngay khi có kết quả, có lẽ bạn 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 một quy tắc định dạng điều kiện đơn giản có công thức =$B25: Hay, bạn có thể xác định mã hàng hóa không hợp lệ bằng cách lồng ghép công thức hàm LEN trong Excel ở trên vào hàm IF: =IF(LEN(LEFT($A2, SEARCH(“-“, $A2) -1))5, “Invalid”, “”)

Đối với công thức xác định mã hàng hóa không phù hợp một cách hoàn hảo dựa trên chiều dài chuỗi, và bạn thậm chí còn không cần một cột đếm ký tự riêng biệt:

Tương tự, bạn có thể dùng hàm LEN trong Excel để đếm số ký tự sau một ký tự cụ thể. Chẳng hạn, trong một danh sách có tên, có thể bạn muốn biết họ của một người có bao nhiêu ký tự. Công thức hàm LEN trong Excel dưới đây sẽ giải quyết việc này: = LEN (RIGHT (A2, LEN(A2) -SEARCH(” “,A2)))

Công thức hoạt động như thế nào: Đầ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 sử dụng hàm SEARCH: SEARCH(“-“,A2))). Sau đó, bạn đếm có bao nhiêu ký tự ở phía sau khoảng trống. Về việc này, bạn lấy tổng chiều dài chuỗi trừ đi vị trí khoảng trống: LEN(A2) -SEARCH(“_”, A2)))

Sau đó, bạn sử dụng hàm RIGHT trong hàm Excel để trả về tất cả các ký tự ở phía sau khoảng trống. Và cuối cùng, bạn sử dụng hàm LEN trong Excel để tính chiều dài chuỗi được trả về bởi hàm RIGHT.

Để có thể ứng dụng tốt các hàm Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của các hàm Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như hàm SUMIFS, hàm COUNTIFS, hàm SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

IX. Ví dụ cách dùng hàm Len 

Dùng hàm LEN trong Excel để phục vụ cho nhân viên văn phòng

Cách đếm tất cả các kí tự trong một ô (bao gồm cả ký tự trống). Như đã nói ở trên, hàm LEN trong Excel đếm tất cả các kí tự trong một ô bao gồm tất cả ký tự trống.

Ví dụ để tính độ dài ô A2, bạn sử dụng công thức sau:=LEN (A2). Cách đếm số ký tự trong nhiều ô: Để đếm số ký tự trong nhiều ô, chọn ô chứa công thức Len sau đó copy nó về các ô khác, có thể bằng cách nhấn nút fill handle (dấu chữ thập màu đen góc phải dưới ô). Sau khi công thức đã được copy, hàm LEN sẽ trả về số ký tự đếm được của mỗi ô.

Ví dụ dưới đây cho thấy hàm LEN trong Excel có thể đếm các ký tự bao gồm chữ cái, chữ số, ký tự trống, dấu phẩy, dấu nháy...

Đếm tổng số ký tự ở một vài ô: Cách dễ nhất để đếm tổng số ký tự ở nhiều ô là tính cộng các hàm LEN với nhau, chẳng hạn: =LEN (A2) + LEN (A3) + LEN (A4). Hoặc sử dụng hàm SUM để tính tổng số ký tự được trả về bởi công thức hàm LEN trong Excel: =SUM (LEN (A2), LEN (A3), LEN (A4))

Ví dụ, trong một danh sách các tên, bạn có thể muốn biết có bao nhiêu ký tự nằm trong Tên. Hàm LEN trong excel dưới đây có thể làm điều đó như sau: =LEN (RIGHT (A2, LEN (A2)-SEARCH (“ “, A2)))

X. Kết

Trên đây là những thông tin bổ ích về cách dùng hàm LEN trong Excel để phục vụ cho nhân viên văn phòng và những người muốn tìm hiểu hơn về các hàm Excel nhé. Chúc các bạn thành công!