Bạn đang cần xác định độ dài của chuỗi kí tự, trong Excel có hàm Len để lấy kết quả chính xác và nhanh nhất về số ký tự cần xác định.Bài viết dưới đây sẽ giới thiệu hàm Len trong Excel, cả về cú pháp và cách sử dụng hàm Len.
Hàm Len trong Excel
Hàm LEN trong Excel dùng để đếm tất cả ký tự trong một ô, rồi trả về chiều dài chuỗi. Cú pháp của hàm Len trong Excel:
=LEN(text)
Text chính là chuỗi ký tự mà bạn muốn thực hiện đếm số ký tự.
=LEN(123) – kết quả trả về 3, bởi vì 3 chữ số được nhập vào câu lệnh text.
=LEN(“good”) – kết quả trả về 4, bởi vì từ good có 4 chữ cái. Giống như bất cứ công thức Excel nào khác, hàm LEN 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 sử dụng công thức LEN, bạn hoàn toàn có thể cần phải nhập tham chiếu ô thay vì chữ số hay chuỗi ký tự, để đếm số ký tự trong một ô cụ thể hay một dải ô.
Ví dụ, để tính được chiều dài chuỗi ký tự trong ô A1, bạn sẽ dùng công thức này:
=LEN(A1)
Cách dùng hàm Len trong Excel – Mẫu công thức
Hàm LEN trông rất đơn giản đến nỗi chẳng cần phải giải thích gì thêm nữa. Tuy nhiên, có một vài mẹo hữu ích có thể giúp bạn điều chỉnh công thức LEN trong Excel theo mong muốn của mình.
Cách đếm tất cả ký tự trong một ô (Bao gồm khoảng trống)
Hàm LEN trong Excel đếm tất cả ký tự trong một ô cụ thể một cách tuyệt đối, bao 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 dữ liệu ô A2, bạn sử dụng công thức này:
=LEN(A2)
Đếm số ký tự trong nhiều ô
Để thực hiện đếm số ký tự trong nhiều ô, bạn hãy chọn ô có công thức LEN rồi 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 sẽ trả về kết quả số ký tự đếm được trong mỗi ô.
Và một lần nữa, bạn hãy lưu ý rằng hàm LEN đếm tất cả mọi thứ một cách tuyệt đối 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 nữa:
Lưu ý: Khi bạn sao chép một công thức xuống các cột khác, hãy chắc rằng bạn sử dụng tham chiếu ô tương đối chẳng hạn như LEN(A1), hay 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 LEN của bạn sẽ điều chỉnh một cách thích hợp với vị trí mới.
Đế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 vài hàm LEN nữa, ví dụ:
=LEN(A2)+LEN(A3)+LEN(A4)
Hay, bạn hãy sử dụng hàm SUM để tính tổng số ký tự được trả về bởi hàm LEN:
=SUM(LEN(A2),LEN(A3),LEN(A4))
Cách nào cũng được, miễn sao 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 sẽ dễ hiểu và cũng dễ sử dụng, nhưng nó không phải là cách hiệu quả nhất để bạn đếm số ký tự trong một dải gồm có, giả sử, 100 hay 1000 ô. Trông trường hợp này, bạn nên dùng hàm SUM và hàm LEN trong công thức mảng.
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 bạn thao tác với bảng tính lớn thì vấn đề phổ biến nhất chính là khoảng cách đầu dòng và cuối dòng, ví dụ: khoảng trống ở đầu hay ở cuối ký tự. Có lẽ bạn hầu như không thấy chúng trên bảng tính, nhưng 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.
Để bạn tính được chiều dài 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 như sau:
=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 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 hơn như sau:
=LEN(SUBSTITUTE(A2,” “,””))
Hàm SUBSTITUTE thay thế ký tự này với ký tự khác. Trong công thức trên, bạn đang thực hiện thay thế 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 đang lồng ghép hàm SUBSTITUTE vào hàm LEN nên phép 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 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 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 vì bạn phải đế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ư sau:
Và tất cả 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ệ? À 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 Excel có dạng như sau:
=LEN(LEFT($A2, SEARCH(“-“, $A2) -1))
Và bây giờ, hãy chia nhỏ công thức ra để bạn có thể hiểu quy luật của nó:
Bạn 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 để 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 =$B2<>5:
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 LEN ở trên vào hàm IF:
=IF(LEN(LEFT($A2, SEARCH(“-“, $A2) -1))<>5, “Invalid”, “”)
Như được diễn giải trong ảnh chụp màn hì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 thậm chí không cần một cột đếm ký tự riêng biệt:
Tương tự, bạn có thể sử dụng hàm LEN trong Excel để đếm số ký tự sau một ký tự cụ thể.
Ví dụ, trong một danh sách 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 LEN sau đâ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 để 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 để tính chiều dài chuỗi được trả về bởi hàm RIGHT.
