Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm LEN trong Excel trong Excel để đếm số ký tự.
Tổng quan về hàm LEN
Hàm LEN Đếm số ký tự trong một chuỗi văn bản.
Để sử dụng Hàm trang tính LEN Excel, hãy chọn một ô và nhập:
(Chú ý cách các đầu vào công thức xuất hiện)
Hàm LEN Cú pháp và đầu vào:
= LEN (văn bản)
chữ - Một chuỗi văn bản.
Chức năng LEN là gì?
Hàm LEN trong Excel, viết tắt của "length", cho bạn biết số ký tự trong một chuỗi văn bản nhất định.
Mặc dù nó chủ yếu được sử dụng cho dữ liệu chuỗi (văn bản), bạn cũng có thể sử dụng nó trên dữ liệu số - nhưng như chúng ta sẽ thấy, có một số điều bạn cần lưu ý.
Cách sử dụng hàm LEN
Để sử dụng Hàm LEN trong Excel, hãy nhập như sau, đặt bất kỳ chuỗi văn bản, tham chiếu ô hoặc công thức nào giữa các dấu ngoặc đơn:
= LEN (B2)
Hãy xem một số ví dụ:
Trong ví dụ số 1, chúng tôi đã hỏi LEN có bao nhiêu ký tự trong ô B2 chứa “Luke” - vì vậy Excel trả về 4.
Cách kết hợp hàm LEN
Nếu bạn muốn trả về tổng số ký tự trong hai ô, bạn có một số tùy chọn. Bạn có thể nối hai ô bằng cách sử dụng dấu và (“&”) như trong ví dụ # 1 hoặc bạn có thể sử dụng LEN trên cả hai ô và tính tổng kết quả, như trong ví dụ # 2.
Lưu ý quan trọng: LEN đếm khoảng trắng dưới dạng ký tự. Hãy xem ví dụ số 3 - trong trường hợp này, LEN trả về 14 vì nó cũng đếm khoảng cách giữa “Luke” và “Skywalker”.
Cách LEN xử lý số
Hàm LEN trong Excel được thiết kế để trả về số ký tự trong một chuỗi văn bản. Nếu bạn chuyển một số, LEN trước tiên sẽ chuyển đổi nó thành một chuỗi, sau đó trả về độ dài của nó.
Như bạn có thể thấy, LEN tính các điểm thập phân và nếu bạn định dạng một ô thành một đơn vị tiền tệ, thì ký hiệu tiền tệ sẽ không được tính.
Cũng lưu ý rằng ngay cả khi định dạng tiền tệ hiển thị các số đến hai chữ số thập phân, LEN không tính các số không ở cuối.
Cách LEN xử lý các ô được định dạng
Hãy xem các ví dụ sau:
Các ô B2 và B3 đều chứa cùng một hình: 3.33333. Vậy tại sao LEN trả về các giá trị khác nhau cho mỗi loại?
Câu trả lời là ở B2 tôi đã nhập giá trị 3.33333, nhưng ở B3 tôi đã nhập công thức = 10/3, và sau đó định dạng nó thành 6 chữ số thập phân.
Với các ô được định dạng, LEN trả về thật sự giá trị của ô, bất kể số vị trí thập phân bạn đã chọn để hiển thị.
Nhưng tại sao lại là 16? Đó là bởi vì Excel có thể lưu trữ tối đa 15 số trong một ô và sẽ cắt các số dài hơn khi chúng đạt đến giới hạn này. Vì vậy, LEN trả về số 16: 15, cộng với dấu thập phân.
LEN xử lý ngày như thế nào
Excel lưu trữ ngày tháng dưới dạng số, được biểu thị bằng số ngày kể từ ngày 0 tháng 1 năm 1900.
Ví dụ # 1 và # 2 là tương đương nhau, chỉ là B2 được định dạng là ngày và B3 là số. Vì vậy, LEN trả về 5 theo một trong hai cách.
Ví dụ # 3 được định dạng là văn bản, vì vậy LEN trả về 10. Điều này giống như nhập = LEN (“27/12/1977”).
Các ví dụ hữu ích về LEN
Bạn có thể kết hợp LEN với một số hàm Excel theo một số cách hữu ích.
Cắt một số ký tự cho trước từ cuối chuỗi
Bạn có thể kết hợp LEN với LEFT để cắt một số ký tự nhất định từ cuối chuỗi. Nếu chuỗi bạn muốn cắt ở A2 và bạn muốn cắt 5 ký tự từ cuối, bạn sẽ thực hiện như sau:
= LEFT (A2, LEN (A2) -5)
Hàm LEFT Excel trả về một số ký tự nhất định từ đầu chuỗi.
Kiểm tra ví dụ dưới đây:
= LEFT (A2, LEN (A2) -5)
Ở đây chúng ta có một dãy số tham chiếu với mã vùng bốn chữ số ở cuối. Chúng tôi đã sử dụng LEN với LEFT để lấy các chuỗi trong cột A và trả về một số ký tự bằng độ dài của chuỗi, trừ đi 5, sẽ loại bỏ bốn chữ số và dấu gạch nối.
Sử dụng LEN mà không cần đếm khoảng trắng ở đầu và cuối
Đôi khi các ô của chúng ta có thể chứa khoảng trắng trước và sau dữ liệu thực tế. Điều này thường xảy ra khi dữ liệu được nhập theo cách thủ công và đôi khi mọi người vô tình nhận được một khoảng trống khi sao chép và dán dữ liệu.
Điều này có thể làm mất kết quả hàm LEN của chúng ta, nhưng chúng ta có thể giải quyết vấn đề đó bằng cách kết hợp LEN với TRIM, như sau:
= LEN (TRIM (A2))
TRIM loại bỏ bất kỳ khoảng trắng ở cuối hoặc ở đầu khỏi một chuỗi. Trong ví dụ dưới đây, mỗi câu đều có khoảng trắng trước hoặc sau văn bản. Lưu ý các kết quả khác nhau giữa LEN và LEN với TRIM.
Sử dụng LEN với SUBSTITUTE để tránh đếm một ký tự đã cho
Nếu bạn muốn đếm số ký tự trong một chuỗi, nhưng bạn không muốn Excel đếm một ký tự cụ thể, bạn có thể thực hiện việc này bằng cách kết hợp LEN với SUBSTITUTE.
Với SUBSTITUTE, bạn có thể lấy một chuỗi và hoán đổi bất kỳ ký tự hoặc chuỗi con nào bên trong nó cho bất kỳ ký tự nào khác. Chúng tôi có thể sử dụng SUBSTITUTE để thay thế ký tự mà chúng tôi không muốn đếm bằng ký tự không có gì, xóa nó một cách hiệu quả.
Ví dụ:
= SUBSTITUTE ("Han Solo", "", "")
Sẽ trở lại:
HanSolo
Vì vậy, hãy tưởng tượng chúng ta có một danh sách các tên và chúng ta muốn biết số ký tự trong mỗi tên. Tuy nhiên, họ và tên được lưu trữ trong cùng một ô và chúng tôi không muốn đếm khoảng cách giữa chúng.
Chúng ta có thể kết hợp LEN với SUBSTITUTE như sau:
= LEN (SUBSTITUTE (A2, "", ""))
Điều này sẽ cho chúng tôi các kết quả sau:
Lưu ý rằng vì ví dụ thứ tư không có khoảng trắng, nên cả hai công thức đều tạo ra cùng một kết quả.
Tìm từ thứ N trong chuỗi
Chúng ta có thể kết hợp TRIM, MID, SUBSTITUTE, REPT với LEN như sau để lấy từ cuối cùng của chuỗi.
= TRIM (MID (SUBSTITUTE (A $ 2, "", REPT ("", LEN (A $ 2))), (B2-1) * LEN (A $ 2) +1, LEN (A $ 2)))
Điều này sẽ cho chúng tôi các kết quả sau:
Trích xuất số từ Trái / Phải của một chuỗi
Khi bạn làm việc với excel, bạn cần phải chính xác số lượng từ các chi tiết đã cho. Đôi khi bạn cần lấy số chính xác từ Trái và đôi khi bạn cần trích xuất số từ Phải. Vì vậy, hàm LEN sẽ giúp bạn thực hiện công việc này bằng cách kết hợp với một vài hàm khác.
Trích số từ bên trái
Chúng ta có thể kết hợp LEFT, SUM, SUBSTITUTE với LEN như sau:
= LEFT (A2, SUM (LEN (A2) -LEN (SUBSTITUTE (A2, {"0", "1", "2", "3", "4", "5", "6", "7" " , "8", "9"}, ""))))
Điều này sẽ cho chúng tôi các kết quả sau:
Trích số từ bên phải
Chúng ta có thể kết hợp RIGHT, MIN, FIND với LEN như sau:
= RIGHT (A2, LEN (A2) -MIN (TÌM ({0,1,2,3,4,5,6,7,8,9}, A2 & "0123456789")) + 1)
Điều này sẽ cho chúng tôi các kết quả sau:
LEN với REPT để thêm ZEROS đứng đầu vào số
Thêm ZEROS hàng đầu rất hữu ích khi bạn làm việc với excel. Trong ví dụ này, chúng tôi sẽ giải thích cách bạn có thể làm điều này bằng cách kết hợp LEN với REPT.
= REPT (0, 6-LEN (A2)) & A2
Trong ví dụ trên, chúng tôi giải thích chỉ thêm các số 0 ở đầu cho đến 5 chữ số, nhưng dựa trên yêu cầu, bạn có thể thay đổi số 6 để đạt yêu cầu. bạn có thể tham khảo ví dụ dưới đây để lấy số 0 đứng đầu lên đến 10 chữ số.
Các ví dụ khác về Thêm ZEROS hàng đầu vào số.
Bảng dưới đây giúp bạn cung cấp thêm các cách để thêm các số không ở đầu vào số thiết lập.
LEN trong Google Trang tính
Hàm LEN hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
Ghi chú bổ sung
Hàm LEN rất hữu ích khi làm việc với các hàm văn bản khác. Chẳng hạn như Trái, Phải hoặc Giữa.
Và cuối cùng, một trong những vai trò hữu ích hơn mà hàm LEN có thể phục vụ là xác nhận phạm vi cần có độ dài cụ thể.
Trong ví dụ này, người dùng cần nhập Zipcode 5 chữ số trong phạm vi A2: A10. Bạn sẽ nhận thấy trong hình rằng mỗi khi người dùng nhập nội dung ô không bằng 5 ký tự, màu của ô sẽ thay đổi, điều này rất dễ kiểm tra.
Điều này đạt được bằng cách sử dụng hàm LEN với định dạng có điều kiện, sau đây là cách thực hiện:
1. Chọn Phạm vi hoặc ô để áp dụng Định dạng có Điều kiện cho
2. Chọn Sự sắp xếp trên Menu chính
3. Chọn Định dạng có điều kiện
4. Trong thay đổi thả xuống Giá trị ô là đến Công thức là
5. Nhập = LEN (A2: A10) 5
6. Nhấp vào Sự sắp xếp… và chọn định dạng để áp dụng nếu điều kiện được đáp ứng. Tôi đã thay đổi Mẫu hoặc màu nền
7. Bấm OK, OK
Đây là đoạn mã của cửa sổ Định dạng có điều kiện của tôi bằng LEN
Ví dụ về LEN trong VBA
Bạn cũng có thể sử dụng hàm LEN trong VBA. Kiểu:application.worksheet Chức năng.len (văn bản)
Đối với các đối số của hàm (văn bản, v.v.), bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
Thực thi các câu lệnh VBA sau
Range ("B2") = Len (Range ("A2")) Range ("B3") = Len (Range ("A3")) Range ("B4") = Len (Range ("A4")) Range ( "B5") = Len (Phạm vi ("A5")) Phạm vi ("B6") = Len (Phạm vi ("A6"))
sẽ tạo ra kết quả sau
Quay lại danh sách tất cả các hàm trong Excel
