Thủ Thuật Excel

Hàm LEFT và các ứng dụng cực hay của hàm Left trong phân tách họ tên

Posted on by Andrew
27
Th9

 

Trong bài viết này, Học Excel Online sẽ giúp các bạn cách sử dụng hàm LEFT trong Excel để phân xuất được chuỗi kí tự con từ phần đầu của chuỗi văn bản, phân xuất văn bản trước một kí tự xác định, buộc hàm LEFT trả về số, và hơn thế…

CÔNG THỨC HÀM LEFT TRONG EXCEL

Hàm LEFT trong Excel được sử dụng để trả về số lượng kí tự nhất định từ mở đầu của một chuỗi.

Công thức: LEFT(text, [num_chars])

Cụ thể:

            Text (bắt buộc): chuỗi văn bản gốc để từ đó phân xuất ra chuỗi kí tự con. Đóng vai trò như khung tham chiếu của ô chứa văn bản.

            Num_chars (không bắt buộc): số lượng kí tự muốn phân xuất, bắt đầu từ phía bên trái của chuỗi.

            Nếu num_chars bị bỏ sót, tự động kết quả sẽ trả về 1 kí tự.

            Nếu num_chars lớn hơn tổng độ dài văn bản, hàm LEFT sẽ trả về toàn bộ văn bản.

Ví dụ, để phân xuất 3 kí tự đầu tiên từ văn bản trong ô A2, dùng công thức:

 =LEFT(A2, 3)

Kết quả trả về:

Lưu ý quan trọng: hàm LEFT thuộc hàm văn bản, bởi vậy kết quả của công thức hàm LEFT luôn là chuỗi văn bản, ngay cả khi giá trị nguồn để phân xuất kí tự là số. Nếu thực hiện hàm LEFT với dữ liệu số và muốn kết quả trả về là số, sử dụng kết hợp với hàm VALUE.

CÁCH SỬ DỤNG HÀM LEFT TRONG EXCEL – CÔNG THỨC VÍ DỤ

Cùng tìm hiểu cách sử dụng hàm LEFT kết hợp với các hàm khác trong Excel để giải quyết những trường hợp phức tạp hơn.

CÁCH PHÂN XUẤT CHUỖI KÝ TỰ CON ĐỨNG TRƯỚC MỘT KÍ TỰ NHẤT ĐỊNH

Trong trường hợp muốn phân xuất một phần của chuỗi văn bản đứng trước một ký tự nhất định. Ví dụ, cần lấy họ (first name) trong cột tên đầy đủ (full name), hay lấy mã quốc gia trừ cột số điện thoại… Khó khăn ở chỗ mỗi tên và mỗi mã lại chứa số lượng kí tự khác nhau, bởi vậy không dễ dàng gì có thể lấy được một số xác định trong num_chars bằng hàm LEFT như ví dụ trên.

Tìm hiểu thêm:  Top 5 khóa học Excel nâng cao chất lượng nhất trên Unica

Nếu họ và tên được phân cách bởi khoảng trống (dấu cách), công việc chuyển sang là tìm vị trí của kí tự khoảng trống trong chuỗi. Công việc này có thể thực hiện dễ dàng với hàm SEARCH hoặc FIND:

=LEFT(A2, SEARCH(” “, A2))

Để phát triển thêm công thức,  loại bỏ khoảng trống bằng cách trừ 1 từ kết quả của hàm SEARCH (không hiển thị trong các ô, dấu cách có thẻ gây nên nhiều vấn đề, đặc biệt là khi bạn định dùng các tên được phân xuất trong các công thức khác).

=LEFT(A2, SEARCH(” “, A2)-1)

Tương tự bạn có thể phân xuất mã quốc gia từ cột số điện thoại. Khác biệt duy hất ở chỗ dùng hàm SEARCH để tìm ra vị trí dấu (“-”) trước khoảng trống:

=LEFT(A2, SEARCH(“-“, A2)-1)

Tóm lại, có thể sử dụng công thức này để có được chuỗi kí tự con đứng trước bất cứ 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ư đã biết, chức năng của hàm LEFT là phân xuất chuỗi kí tự con từ đầu chuỗi văn bản. Nhưng nếu muốn dời một số kí tự nhất định từ phần cuối của chuỗi và đặt phần còn lại của chuỗi vào một ô khác? Trường họp này có thể kết hơp 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 tên nguyên tắc: hàm LEN lấy tổng số kí tự trong một chuỗi. Sau đó trừ đi số lượng kí tự không mong muốn từ tổng độ dài của dãy, hàm LEFT sẽ trả về số kí tự còn lại.

Ví dụ, để dời 7 kí tự cuối cùng của văn bản trong ô A2, dùng công thức:

Tìm hiểu thêm:  Cách tìm kiếm dữ liệu trong excel đơn giản cho người mới

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

Bảng kết quả bên dưới cho thấy công thức đã cắt phần “ – ToDo” (gồm 4 chữ cái, 1 dấu gạch ngang, và 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ư đã biết, hàm LEFT luôn trả kết quả về là văn bản chữ, ngay cả khi đặt một vài chữ số đầu tiên của một con số. Kết quả trả về của hàm LEFT không thể dùng để tính toán hay dùng trong những hàm khác trong Excel tạo thiết lập số.

Bởi vậy, làm thế nào để kết quả của hàm LEFT trả về là con số thay vì chuỗi văn bản? Cách đơn giản là sử dụng hàm VALUE – hàm được thiết kế để chuyển chuỗi con số sang con số, sử dụng: VALUE(LEFT())

Ví dụ, để phân xuất 2 kí tự đầu tiên của chuỗi trong ô A2 và chuyển kết quả sag con số, dùng công thức:

=VALUE(LEFT(A2,2))

Kết quả trả về tương tự bảng sau:

Như trong bảng, cột B thu được kết quả là những con số từ công thức hàm VALUE LEFT. Khi Excel nhận kết quả là những con số, phép tính tổng, tính trung bình giá trị, tìm giá trị cao nhất – thấp nhất, hay bất cứ phép toán nào đều có thể thực hiện được.

HÀM LEFT KHÔNG THỰC HIỆN ĐƯỢC – NGUYÊN NHÂN VÀ CÁCH KHẮC PHỤC

Nếu hàm LEFT không thực hiện được trong trang tính, hầu hết là do những nguyên nhân sau:

  1. NUM_CHARS NHỎ HƠN O

Nếu công thức hàm LEFT trả về #VALUE (lỗi), đầu tiên cần phải kiểm tra lại giá trị của num_chars. Nếu là số âm, chỉ cần loại bỏ các cấu trừ, lỗi sẽ được khắc phục.

Thông thường, lỗi thường xả ra khi num_chars được thể hiện bằng những chức năng khác. Trong trường hợp này, copy hàm đó vào một ô khác hoặc chọn trên thanh công thức, nhấn F9 để xem hàm tương đương. Nếu giá trị nhỏ hơn 0, kiểm tra lại hàm lỗi.

Ví dụ, để phân xuất mã quốc gia từ số điện thoại: LEFT(A2, SEARCH(“-“, A2)-1). Hàm SEARCH trong phần num_chars sẽ tìm vị trí của dấu (-) đầu tiên trong chuỗi nguồn, từ đó ra trừ 1 để xóa dấu (-) khỏi kết quả sau cùng. Nếu thay thế -1 với -11, công thức sẽ trả vè kết quả lỗi #VALUE vì num_chars tương đương với số âm:

Tìm hiểu thêm:  4 cách lấy dữ liệu từ bảng này sang bảng khác trong Excel chi tiết

tương đương với số âm:

  1. KHOẢNG TRỐNG ĐỨNG ĐẦU TRONG VĂN BẢN NGUỒN

Trong trường hợp hàm LEFT lỗi không rõ lí do, kiểm tra những khoảng trống (dấu cách) ở đầu giá trị nguồn. Nếu đã thực hiện copy dữ liệu trừ trang web hay từ nguồn bên ngoài, sẽ có rất nhiều dấu cách ẩn đứng trước văn bản, điều này khó nhận ra cho đến khi công thức hàm báo lỗi, như hình trong bảng sau:

Để loại bỏ những dấu cách ở đầu trang tính, sử dụng hàm TRIM hoặc bổ sung hàm CELL CLEANER.

  1. HÀM LEFT KHÔNG THỰC HIỆN ĐƯỢC VỚI NGÀY THÁNG

Nếu cố sử dụng hàm LEFT để phân xuất một phần trong ngày (như ngày, tháng, năm), trong hầu hết trường hợp, kết quả trả về sẽ là một vài con số thể hiện ngày tháng. Bởi trong Excel, tất cả ngày tháng được lưu dưới dạng số nguyên đại diện cho số ngày; từ 1/1/1900 là số 1 (do định dạng Excel). Trong ô hiển thị chỉ là biểu diễn trực quan của ngày, và biểu diễn đó có thể thay thế bằng cách sử dụng định dạng ngày khác.

Ví dụ, trong ô A1 có 11- tháng 1 – 2017, khi thực hiện phân xuất bằng công thức hàm LEFT(A1,2), kết quả trả về sẽ là 42 – 2 chữ số đầu tiên trong số 42746 biểu thị ngày 11/1/2017 trong hệ thống Excel.

Để phân xuất một phần xác định trong ngày, sử dụng các hàm DAY, MONTH, hoặc YEAR

Trường hợp ngày được nhập bằng chuỗi văn bản, hàm LEFT sẽ hiệu quả, kết quả thể hiện như hình dưới đây:

Trên đây là hướng dẫn của Học Excel Online về cách sử dụng hàm LEFT trong Excel. Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…