Bạn đang không biết làm thế nào để đếm số ký tự trong một ô trong Excel? Đừng lo lắng trong bài viết dưới đây thủ thuật vn sẽ hướng dẫn dưới bạn cách sử dụng hàm LEN để đếm số ký tự trong ô kể cả khoẳng trắng giữa các từ.
Hàm LEN là gì?
Len cũng là một trong các hàm cơ bản trong Excel được sử dụng nhiều và dễ sử dụng nhất. Len viết tắt của “length” có nghĩa là độ dài và đó cũng chính là tác dụng thật sự của hàm LEN khi nó về chiều dài của chuỗi ký tự, hay chiều dài của một ô trong Excel.
Nói cách khác, chúng ta có thể sử dụng hàm LEN trong Excel để đếm tất cả ký tự trong một ô bao gồm chữ cái, chữ số, ký tự đặc biệt, vả tất cả những khoảng trống (trắng).
Trong bài hướng dẫn dưới đây thủ thuật vn sẽ cùng các bạn tìm hiểu nhanh cú pháp hàm LEN trước, sau đó sẽ tìm hiểu kỹ một vài mẫu công thức để đếm số ký tự trong Excel các bạn nhé.
Tác dụng của hàm LEN trong Excel
Hàm LEN trong Excel đếm tất cả ký tự trong một ô, rồi trả về chiều dài chuỗi.
Cú pháp hàm LEN
=LEN(text)
Trong đó:
- Text chính là chuỗi ký tự nơi mà bạn muốn đếm số ký tự hoặc ô trong Excel
Chúng ta sẽ xét 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(155) – trả về 3, bởi vì 3 chữ số được nhập vào câu lệnh text.
=LEN("NAMXUYEN") cho kết là 8 bởi vì từ NAMXUYEN có 8 chữ cái. Giống như bất cứ công thức Excel nào khác, với 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à chắc chắn rồi dấu ngoặc kép này khôn được đêm.
Chúng ta có thể 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 ô C5, bạn sẽ dùng công thức này:
=LEN(C5)
Ví dụ minh họa sử dụng hàm LEN trong Excel.
Cách đếm tất cả các ký tự trong một ô ( bao gồm cả khoảng trắng)
Như đã nói lúc trước, 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 hay thụt đầu dòng, thụt cuối dòng, và cả khoảng trống giữa các từ.
Ví dụ, để tính chiều dài của ô A5, bạn sử dụng công thức này:
=LEN(A5)
Như được diễn giải trong ảnh chụp màn hình dưới đây, công thức LEN đếm được 28ý tự, bao gồm các chữ cái và khoảng trống.
Đếm số ký tự trong nhiều ô
Để đếm số ký tự trong nhiều ô, 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ề số ký tự đếm được trong mỗi ô.
Và một lần nữa, 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 sao chép một công thức xuống các cột khác, các bạn nên sử dụng tham chiếu tương đối chẳng hạn như LEN(A3), hay một tham chiếu hỗn hợp như LEN($A3) 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à sử dụng nhiều hàm LEN.
Ví dụ
=LEN(A5)+LEN(A7)+LEN(A6)
Hoặc có thể sử dụng hàm SUM để tính tổng số ký tự được trả về bởi hàm LEN:
=SUM(LEN(A5),LEN(A7),LEN(A6) )
Cách đếm số ký tự không tính khoảng trống dầu dòng và cuối dòng
Khi làm việc với bảng tính lớn thì vấn đề phổ biến chính là khoảng cách đầu dòng và cuối dòng. Nếu bạn 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 rất nhiều vì nó đếm tất cả khoảng trống trong phép đếm ký tự:
Để 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, hãy nhớ lồng ghép hàm TRIM vào hàm LEN trong Excel:
=LEN(TRIM(C5))
Cách đếm số ký tự trong một ô không tính các khoảng trống
Nếu mục đích của bạn chỉ là đếm số ký tự mà không đếm cả 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:
=LEN(SUBSTITUTE(C5," ",""))
- Chúng ta sử dụng hàm SUBSTITUTE thay thế ký tự này với ký tự khác. Trong công thức trên, bạ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 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
Ví dụ bạn có một danh sách mã hàng hóa như sau:
Mã Hàng
ABCDE-091
BCDJ-082
JKQLF-0091
KLJGH-9001
MNOPQRS-BCD
Yêu cầu là chỉ ra những mã hàng không hợp lệ biết rằng mã hàng hợp lệ đều có 5 ký tự trước dấu gạch ngang (-)
Cách giải quyết đơn giản là chúng ta cần đếm số ký tự trong nhóm đầu tiên và so sánh với 5.
Công thức áp dụng cho bài toán trên 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ụ 2:
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))) với A2 là ô chứa họ và tên.
Lời kết
Qua từng ví dụ cụ thể ở trên chắc hẳn bạn đã nắm rõ cách sử dụng hàm LEN trong Excel rồi chứ. Hi vọng bài viết này giúp ích được cho các bạn. Chúc các bạn học tốt!
