Với khả năng phân xuất được chuỗi kí tự con có điều kiện trong một văn bản cho trước, hàm LEFT trong Excel được ứng dụng khá phổ biến với người dùng. Hãy cùng tìm hiểu chi tiết hơn trong bài viết bên dưới nhé!
Nội dung bài viết
CÔNG THỨC HÀM LEFT TRONG EXCEL
Hàm LEFT có công dụng để trả về số lượng các kí tự nhất định từ mở đầu của một chuỗi.
Công thức như sau: LEFT(text, [num_chars])
Cụ thể:
Text (bắt buộc): là chuỗi văn bản gốc để phân xuất ra chuỗi kí tự con, là khung tham chiếu của ô chứa văn bản.
Num_chars (không bắt buộc): là số lượng kí tự mà bạn muốn phân xuất, bắt đầu từ bên trái của chuỗi.
Nếu num_chars bị bỏ sót, thì tự động kết quả sẽ trả về 1 kí tự.
Ví dụ, bạn muốn phân xuất 3 kí tự đầu tiên từ văn bản trong ô A2, hãy sử dụng công thức:
=LEFT(A2, 3)
Kết quả trả về lúc này là:
Lưu ý là hàm LEFT thuộc về hàm văn bản, nên kết quả của công thức hàm LEFT sẽ luôn là chuỗi văn bản, kể cả khi giá trị nguồn để phân xuất kí tự là dạng số. Nếu bạn thực hiện hàm LEFT với dữ liệu số và mong muốn kết quả trả về là số, hãy dùng kết hợp với hàm VALUE.
CÁCH DÙNG HÀM LEFT TRONG EXCEL – CÔNG THỨC VÍ DỤ
CÁCH PHÂN XUẤT CHUỖI KÝ TỰ CON TRƯỚC MỘT KÍ TỰ NHẤT ĐỊNH
Nếu bạn đang muốn phân xuất một phần của chuỗi văn bản trước một ký tự nhất định. Chẳng hạn như, bạn cần lấy họ (first name) trên cột tên đầy đủ (full name), hoặc là lấy mã quốc gia trừ cột số điện thoại…Điểm khó khăn nằm ở chỗ mỗi tên và mỗi mã lại đang chứa số lượng kí tự khác nhau, vì vậy không dễ dàng gì để có thể lấy được một số xác định trong num_chars bằng việc dùng hàm LEFT như ví dụ trên.
Nếu họ và tên được phân cách bằng khoảng trống (dấu cách), việc chuyển sang là tìm vị trí của kí tự khoảng trống trong chuỗi. Vấn đề này có thể thực hiện một cách dễ dàng với hàm SEARCH hoặc hàm FIND:
=LEFT(A2, SEARCH(” “, A2))
Để có thể phát triển thêm công thức, cũng như loại bỏ khoảng trống bằng việc trừ 1 từ kết quả của hàm SEARCH (không hiển thị trong những ô, dấu cách có thể gây nên nhiều vấn đề, đặc biệt là lúc bạn định dùng những tên được phân xuất trong những công thức khác).
=LEFT(A2, SEARCH(” “, A2)-1)
Tương tự như vậy, bạn có thể phân xuất mã quốc gia từ cột số điện thoại. Điểm khác biệt duy nhất ở đây là dùng hàm SEARCH để tiến hành tìm ra vị trí dấu (“-”) trước khoảng trống:
=LEFT(A2, SEARCH(“-“, A2)-1)
Tóm lại, bạn có thể dùng công thức này để có được chuỗi kí tự con đứng trước bất kì kí tự nào:
LEFT(chuỗi, SEARCH (kí tự,chuỗi)-1)
CÁCH DỜI N KÍ TỰ CUỐI KHỎI CHUỖI KÍ TỰ
Như bạn đã biết, chức năng của hàm LEFT chính là phân xuất chuỗi kí tự con từ đầu của một chuỗi văn bản. Nhưng nếu như bạn muốn dời một số kí tự nhất định từ vị trí phần cuối của chuỗi, rồi đặt phần còn lại của chuỗi vào một ô khác thì làm thế nào? Trường hợp này thì bạn có thể kết hơp sử dụng hàm LEFT và LEN:
LEFT(chuỗi, LEN(chuỗi) – số_kí_tự_muốn_dời)
Công thức hoạt động dựa trên nguyên tắc: hàm LEN sẽ lấy tổng số kí tự có trong một chuỗi. Sau đó tiến hành trừ đi số lượng kí tự không mong muốn từ tổng độ dài của dãy, và hàm LEFT sẽ trả về số kí tự còn lại.
Ví dụ như, để dời 7 kí tự cuối cùng của văn bản trong ô A2, bạn dùng công thức:
=LEFT(A2, LEN(A2)-7)
Bảng kết quả bên dưới đây cho thấy công thức này đã cắt phần “ – ToDo” (gồm 4 chữ cái, 1 dấu gạch ngang, cùng 2 khoảng trống) từ chuỗi văn bản trong cột A.
CÁCH BUỘC HÀM LEFT TRẢ KẾT QUẢ VỀ LÀ MỘT SỐ
Như bạn đã biết, hàm LEFT sẽ luôn trả kết quả về là văn bản chữ, kể cả khi đặt một vài chữ số đầu tiên trong một con số. Kết quả trả về lúc này của hàm LEFT không thể sử dụng để tính toán hay dùng trong các hàm khác trong Excel tạo thiết lập số.
Vậy thì làm thế nào để kết quả của hàm LEFT trả về sẽ là con số thay cho chuỗi văn bản? Cách đơn giản ở đây đó là dùng hàm VALUE – hàm được thiết kế để tiến hành chuyển chuỗi con số thành con số, sử dụng: VALUE(LEFT())
Ví dụ, bạn muốn phân xuất 2 kí tự đầu tiên ở chuỗi trong ô A2 rồi chuyển kết quả sang con số, hãy dùng công thức:
=VALUE(LEFT(A2,2))
Kết quả trả về sẽ như bảng sau:
Dựa vào bảng bạn có thể thấy, cột B thu được kết quả là các con số từ công thức hàm VALUE LEFT. Khi Excel nhận kết quả là các con số, phép tính tổng, trung bình giá trị, tìm giá trị cao nhất – thấp nhất, hoặc là bất kì phép toán nào đều có thể hoàn thành được.
Xem thêm: Cách dùng hàm ROUND trong Excel
HÀM LEFT KHÔNG THỰC HIỆN ĐƯỢC – NGUYÊN NHÂN VÀ CÁCH KHẮC PHỤC
Nếu như hàm LEFT không thực hiện được trong trang tính, thì hầu hết là do các nguyên nhân dưới đây:
-
NUM_CHARS NHỎ HƠN O
Nếu như công thức hàm LEFT trả về #VALUE (lỗi), đầu tiên bạn cần phải kiểm tra lại giá trị của num_chars. Nếu như là số âm, bạn chỉ cần loại bỏ những cấu trừ, phần lỗi sẽ được khắc phục.
Thường thì lỗi thường xả ra khi num_chars được hiển thị bởi các chức năng khác. Ở trường hợp này, bạn thực hiện copy hàm đó vào một ô khác hoặc là chọn trên thanh công thức, sau đó nhấn F9 để xem hàm tương đương. Nếu như giá trị nhỏ hơn 0, hãy kiểm tra lại hàm lỗi.
Ví dụ như, để thực hiện phân xuất mã quốc gia từ số điện thoại: LEFT(A2, SEARCH(“-“, A2)-1). Hàm SEARCH nằm trong phần num_chars sẽ tiến hành tìm vị trí của dấu (-) đầu tiên trong chuỗi nguồn, sau đó ra trừ 1 để xóa dấu (-) khỏi kết quả sau cùng. Nếu như thay thế -1 với -11, công thức lúc này sẽ trả về kết quả lỗi #VALUE vì num_chars tương đương với số âm:
-
KHOẢNG TRỐNG ĐỨNG ĐẦU TRONG VĂN BẢN NGUỒN
Ở trường hợp hàm LEFT lỗi mà bạn không rõ lí do, hãy kiểm tra các khoảng trống (dấu cách) tại đầu giá trị nguồn. Nếu đã thực hiện copy dữ liệu ở trang web hay từ nguồn bên ngoài, sẽ có rất nhiều dấu cách ẩn đứng phía trước văn bản. Điều này sẽ khó nhận ra cho đến khi công thức hàm báo lỗi, minh hoạt như hình trong bảng sau:
Để có thể loại bỏ các dấu cách ở đầu trang tính, bạn hãy sử dụng hàm TRIM hoặc là bổ sung hàm CELL CLEANER.
-
HÀM LEFT KHÔNG THỰC HIỆN ĐƯỢC VỚI NGÀY THÁNG
Nếu như cố dùng hàm LEFT để phân xuất một phần trong ngày (như ngày, tháng, năm), thì trong đa số trường hợp, kết quả trả về sẽ chỉ là một vài con số thể hiện ngày tháng. Bởi vì trong Excel, toàn bộ ngày tháng được lưu dưới dạng số nguyên đại diện cho số ngày; 1/1/1900 là số 1 (do định dạng Excel). Tại ô hiển thị chỉ là biểu diễn trực quan của ngày, biểu diễn đó có thể được thay thế bởi cách dùng định dạng ngày khác.
Ví dụ, tại ô A1 có 11- tháng 1 – 2017, khi bạn thực hiện phân xuất bởi công thức hàm LEFT(A1,2), kết quả lúc này trả về sẽ là 42 – 2 chữ số đầu tiên trong số 42746 biểu thị ngày 11/1/2017 tại hệ thống Excel.
Để thực hiện phân xuất một phần xác định trong ngày, bạn hãy dùng các hàm DAY, MONTH, hoặc là YEAR
Trường hợp ngày sẽ được nhập bằng chuỗi văn bản, nên hàm LEFT sẽ hiệu quả, kết quả được thể hiện như hình dưới đây:
Xem thêm: Cách dùng hàm LARGE và hàm SMALL trong Excel
