Hàm LEN – Cách sử dụng

Cú pháp

Cú pháp của hàm LEN như sau:

=LEN(text)

Hàm LEN chỉ chấp nhận một đối số bắt buộc.

Đối số:

text – Đối số này có thể là một chuỗi văn bản được nhập trong dấu ngoặc kép, một số, hoặc một tham chiếu ô chứa chuỗi.

Các đặc điểm quan trọng của hàm LEN

• Nếu ô chứa lỗi, hàm LEN sẽ trả về cùng lỗi đó.
• Hàm LEN bỏ qua định dạng số của dữ liệu.
• Độ dài của một chuỗi rỗng là 0.

Ví dụ về hàm LEN

Vì hàm LEN tính độ dài của chuỗi đầu vào, chúng ta sẽ thử qua một số ví dụ cơ bản.

Ví dụ 1 – Sử dụng đơn giản hàm LEN

Chúng ta có một danh sách tên phim ở cột B và dùng hàm LEN để tính độ dài của mỗi tên phim.

Công thức:

=LEN(B2)

Khi tính độ dài của tên phim, hàm LEN bao gồm chữ cái, số, các dấu cách, ký tự đặc biệt và ký tự không hiển thị. Ví dụ ở ô B6 chứa một biểu tượng cảm xúc, hàm LEN cũng sẽ tính được. Từ “Interstellar” có 12 ký tự; cộng thêm một dấu cách và một ký tự đặc biệt, tổng độ dài là 14. Ví dụ cuối cùng có chuỗi dài 4 ký tự, nhưng hàm LEN trả về 5 vì chuỗi chứa một ký tự không hiển thị.

Bây giờ chúng ta đã biết hàm LEN tính gì, hãy xem nó hoạt động như thế nào với dữ liệu tiền tệ, ngày tháng và thời gian.

Ví dụ 2 – Hàm LEN với dữ liệu có định dạng số

Excel cho phép định dạng số (ngày, giờ, tiền tệ, …) mà không thay đổi giá trị thực. Do đó, khi áp dụng hàm LEN lên dữ liệu đã định dạng, hàm sẽ trả về độ dài của giá trị thực, không phải của giá trị hiển thị.

Trong ví dụ này chúng ta có các định dạng số khác nhau ở cột C, chẳng hạn ngày, giờ, tiền tệ và số thông thường được làm tròn đến 5 chữ số thập phân.

Công thức:

=LEN(C3)

Các giá trị trả về của hàm LEN nằm ở cột D. Ở ô C3, ngày được hiển thị dưới dạng mm/dd/yyyy (10 ký tự), nhưng hàm LEN trả về 5 vì ngày thực chất là số sê-ri 44844. Ở ô C4, thời gian hiển thị dạng hh:mm, nhưng thực tế Excel lưu dưới dạng số thập phân 0.729166666666667, nên LEN trả về 17. Ở ô C5, định dạng tiền tệ được bỏ qua và LEN trả về 4 ký tự (số 1000). Ở ô C6, giá trị là =5/7.

Sử dụng nút Tăng số thập phân trong nhóm Số, chúng ta mở rộng số ở C6 tới dạng thập phân đầy đủ. Khi tính cả 0 và dấu thập phân, tổng số ký tự là 17. Hàm LEN trả về độ dài của kết quả đầy đủ thay vì giá trị đã làm tròn.

Vì vậy, hàm LEN luôn trả về độ dài của giá trị thực, không phải của giá trị đã định dạng hiển thị.

Ví dụ 3 – Loại bỏ dấu cách thừa

Chúng ta thường tải dữ liệu từ internet để phân tích hoặc tham khảo. Khi tải về, dữ liệu thường chứa các dấu cách không cần thiết và ký tự không hiển thị, khiến hàm LEN tính sai.

Để khắc phục, chúng ta có thể kết hợp hàm LEN với TRIM và CLEAN.

Trong ví dụ này, chúng ta tải một vài câu trích dẫn nổi tiếng từ internet để đăng lên mạng xã hội. Giả sử bài đăng có giới hạn ký tự, chúng ta cần kiểm tra độ dài của các câu trước khi đăng.

Dữ liệu tải về có các dấu cách thừa và một dòng cuối cùng còn ngắt dòng. Độ dài chuỗi nguyên dạng sẽ bao gồm mọi dấu cách và ký tự không hiển thị, vì vậy không chính xác.

Công thức loại bỏ dấu cách và ngắt dòng:

=LEN(CLEAN(TRIM(B3)))

Sau khi làm sạch, chúng ta có thể kiểm tra độ dài câu có nằm trong giới hạn ký tự hay không.

Để lấy câu đã làm sạch, chỉ cần:

=CLEAN(TRIM(B3))

So sánh với độ dài chuỗi trước khi làm sạch (cột D), giá trị LEN cơ bản cao hơn đáng kể. Sau khi làm sạch, hàm LEN cho ra giá trị chính xác hơn.

Ví dụ 3.1 – Độ dài không có dấu cách

Trong ví dụ này, chúng ta có danh sách địa chỉ email để gửi thư. Theo tiêu chuẩn, độ dài tối đa của một địa chỉ email là 320 ký tự và không chấp nhận dấu cách.

Vì danh sách được tải về, cần chắc chắn không có dấu cách và tổng độ dài mỗi địa chỉ không vượt quá 320.

Công thức tính độ dài không có dấu cách:

=LEN(SUBSTITUTE(B3," ",""))

SUBSTITUTE thay thế tất cả các dấu cách (” “) bằng chuỗi rỗng (“”), sau đó LEN tính độ dài của chuỗi kết quả.

Nếu giá trị LEN nhỏ hơn 320, chúng ta có thể loại bỏ mọi dấu cách để có địa chỉ email cuối cùng:

=IF(C3<320,SUBSTITUTE(B3," ",""))

Ví dụ 4 – Loại bỏ ký tự cuối bằng hàm LEN

Hàm LEN thường được dùng kết hợp với các hàm Excel khác để thao tác chuỗi văn bản. Một trong những ứng dụng phổ biến nhất là kết hợp với LEFT và RIGHT. Giả sử bộ dữ liệu chứa tên các vận động viên nữ cùng với cân nặng, trong đó cân nặng có ghi kèm đơn vị "pounds".

Để tính trung bình cân nặng, trước tiên phải loại bỏ phần "pounds". Công thức:

=VALUE(LEFT(C3,LEN(C3)-7))

LEFT lấy một số ký tự nhất định từ phía trái của chuỗi. Độ dài cần lấy được tính bằng cách trừ độ dài của từ "pounds" (cùng với một dấu cách) – tức là 7 ký tự – khỏi độ dài tổng của chuỗi.

Thực tế công thức trung gian là:

=LEFT(C3,LEN(C3)-7)

Chúng ta đã loại bỏ các ký tự đơn vị thừa. Để chuyển kết quả sang dạng số, bọc nó bằng VALUE.

Bây giờ có thể tính trung bình cân nặng bằng công thức trực tiếp vì dữ liệu đã sạch và ở dạng số.

Ví dụ 5 – Đếm số từ trong một chuỗi

Chúng ta có một bộ dữ liệu gồm một vài câu trích dẫn truyền cảm hứng và muốn đếm số từ trong mỗi câu. Số từ trong một câu thường bằng số dấu cách cộng 1. Hàm LEN giúp thực hiện logic này trong Excel.

Công thức từng bước:

• Độ dài chuỗi có dấu cách thường:

=LEN(TRIM(B3))

• Độ dài chuỗi không có dấu cách:

=LEN(SUBSTITUTE(B3," ",""))

• Công thức cuối cùng:

=LEN(TRIM(B3))-LEN(SUBSTITUTE(B3," ",""))+1

Như vậy, chúng ta đã có số từ trong mỗi chuỗi.

Hy vọng bạn đã nắm rõ mọi khía cạnh của hàm LEN. Hãy thực hành và khám phá thêm nhiều cách sử dụng hàm LEN trong các bài tập Excel tiếp theo.