Hàm Left và các ứng dụng cực hay của hàm Left trong Excel

Hàm left là một trong số những hàm excel thông dụng nhất, được sử dụng nhiều trong tin học văn phòng. Để hiểu rõ hơn về cách sử dụng hàm và một số lỗi thường gặp, 123job sẽ cung cấp cho bạn thông tin cần thiết trong bài viết dưới đây…

Hàm LEFT là một hàm khá cơ bản của phần mềm Microsoft Excel trong quá trình xử lý các ký tự. Tác dụng của hàm quan trọng này là xác định và lấy phần ký tự bên trái trong 1 chuỗi ký tự. Nhưng không phải lúc nào hàm này cũng có thể xác định ngay được cần lấy bao nhiêu ký tự. Khi đó chúng ta phải kết hợp sử dụng hàm Left với một số hàm khác mới đạt được kết quả. Hãy cùng 123job.vn tìm hiểu cách tận dụng tốt nhất hàm left trong excel nhé!

I. Mô tả hàm left trong excel

Hàm LEFT trong Excel là một trong các hàm Excel cơ bản có tác dụng trả về một hoặc nhiều ký tự đầu tiên trong một chuỗi, trên cơ sở dựa vào số ký tự mà bạn chỉ định trong hàm, hàm này thường được người dùng gọi là hàm cắt chuỗi ký tự bên trái. Bên cạnh hàm LEFT Excel thì còn có hàm RIGHT là hàm có chức năng cắt chuỗi ký tự bên phải, hàm MID trong excel là hàm thực hiện lấy chuỗi ký tự ở giữa.

II. Công thức hàm Left trong Excel

Hàm LEFT trong Excel là một trong số các hàm excel cơ bản hữu ích, được sử dụng nhằm mục đích trả về số lượng ký tự nhất định từ mở đầu của một chuỗi.
Công thức của hàm Left Excel: LEFT(text, [num_chars])
Cụ thể:

• Text (là trường bắt buộc): đây là chuỗi văn bản gốc để từ đó phân xuất ra lần lượt những chuỗi ký tự con. Text trong hàm Left Excel đóng vai trò như khung tham chiếu tương ứng của ô chứa văn bản.
• Num_chars (trường này không bắt buộc): số lượng ký tự mà bạn muốn phân xuất, nó sẽ bắt đầu từ phía bên trái của chuỗi.
• Nếu num_chars trong hàm Left Excel bị bỏ sót, tự động kết quả sẽ trả về là 1 kí tự.
• Nếu num_chars có độ dài lớn hơn tổng độ dài văn bản, hàm LEFT Excel sẽ trả về toàn bộ văn bản.

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

=LEFT(A2, 3)

Kết quả trả về tương ứng là:

Phân xuất ra tương ứng 3 ký tự đầu tiên

Lưu ý quan trọng: hàm LEFT trong các hàm Excel cơ bản là 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à dữ liệu chuỗi văn bản, ngay cả khi giá trị nguồn để phân lại xuất ký tự là dạng số. Nếu thực hiện hàm LEFT trong các hàm Excel cơ bản với dữ liệu số và muốn kết quả trả về là số, bạn nên sử dụng kết hợp với hàm VALUE.

III. Cách sử dụng hàm Left trong Excel - ví dụ minh họa

1. 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 mà bạn muốn phân xuất một phần của chuỗi văn bản tương ứng đứng trước một ký tự nhất định. Ví dụ trong các hàm trong Excel cơ bản, cụ thể là hàm Left, khi 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ỗ là mỗi tên và mỗi mã lại lần lượt chứa số lượng ký tự khác nhau, bởi vậy không hề dễ dàng gì để có thể lấy được một số xác định được nằm trong trường trong num_chars bằng hàm LEFT như ví dụ trên.

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

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

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

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

Phát triển thêm công thức

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

Phân xuất bằng hàm Left mã quốc gia từ cột số điện thoại

Tóm lại, bạn hoàn toàn có thể sử dụng công thức này để xuất được chuỗi ký tự con đứng vị trí đằng trước bất cứ ký tự nào:

LEFT(chuỗi, SEARCH (ký tự,chuỗi)-1)

2. Cách dời n ký tự cuối khỏi ký tự

Như đã biết, chức năng đặc biệt của hàm LEFT là phân xuất chuỗi ký tự con tính từ đầu chuỗi văn bản. Nhưng nếu trong trường hợp bạn muốn dời một số ký tự nhất định từ phần cuối của chuỗi và thực hiện đặ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 hợp lý việc dùng hàm LEFT và LEN:

=LEFT(chuỗi, LEN(chuỗi) – số_kí_tự_muốn_dời)

Công thức hàm Left trên hoạt động trên nguyên tắc: hàm LEN có chức năng lấy tổng số ký tự trong một chuỗi. Sau đó trừ đi số lượng ký tự mà bạn không mong muốn từ tổng độ dài số chữ cái của dãy, hàm LEFT sau đó sẽ trả về số ký tự còn lại.

Ví dụ, để thực hiện dời 7 ký tự cuối cùng của văn bản trong ô A2 của Excel, chúng ta sẽ dùng công thức:

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

Bảng kết quả bên dưới ta có thể thấy công thức đã cắt phần “ – ToDo” (bao gồm 4 chữ cái, 1 dấu gạch ngang, và cả 2 khoảng trống) từ chuỗi văn bản được sử dụng trong cột A.

Công thức đã cắt phần “ – ToDo”

3. Cách buộc hàm Left trong Excel trả kết quả về là một số 

Như đã biết, hàm LEFT trong Excel là hàm luôn trả kết quả về là văn bản chữ, ngay cả khi chúng ta đặt một vài chữ số đầu tiên của một con số. Kết quả tương ứng trả về của hàm LEFT không thể được sử dụng để tính toán hay dùng trong những hàm khác trong Excel để có thể tạo thiết lập số.

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

Ví dụ, để có thể phân xuất 2 ký tự đầu tiên từ chuỗi được đặt trong ô A2 và chuyển kết quả sang con số, chúng ta dùng công thức:

=VALUE(LEFT(A2,2))

Kết quả hàm Left trả về tương tự bảng sau:

Kết quả hàm Left

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

IV. Hàm Left không thực hiện được - nguyên nhân và cách khắc phục

1. Num_Chars nhỏ hơn 0

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

Thông thường, lỗi này trong hàm Left thường xảy 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, có thể copy hàm đó vào một ô khác hoặc chọn trên thanh công thức, sau đó nhấn F9 để xem hàm tương đương. Nếu giá trị nhỏ hơn 0, chúng ta kiểm tra lại hàm lỗi.

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

Num_Chars nhỏ hơn 0

2. Khoảng trống đứng đầu trong văn bản nguồn

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

Khoảng trống đứng đầu trong văn bản nguồn

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

3. Hàm left không thực hiện được với ngày tháng

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

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

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

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

Hàm left không thực hiện được với ngày tháng

V. Cách sử dụng hàm Left nâng cao

1. Hàm Left kết hợp với hàm Search

Cho bảng dữ liệu:

Hàm Left kết hợp với hàm Search

Yêu cầu: Thực hiện tách phần tên trong mỗi đoạn email được cho trước (phần ký tự trước dấu @)

Phần ký tự chúng ta cần lấy nằm ở bên trái của đoạn text, do đó ta có thể sử dụng hàm LEFT. Nhưng gặp phải vấn đề là số ký tự cần lấy lại khác nhau. Do đó ta tạm thời không thể xác định ngay được tham số num_char trong hàm left. Để xác định được rõ số ký tự cần lấy, chúng ta sẽ căn cứ tương ứng vào ký tự @.

Cách làm sẽ là như sau:

• Dùng hàm Search chúng ta sẽ xác định vị trí dấu @ trong các ô ở cột A
• Số ký tự cần lấy ra sau đó của hàm LEFT là phần trước ký tự @, nên có thể viết là num_char = kết quả hàm Search – 1

Thực hiện tách phần tên trong mỗi đoạn email

Công thức tại ô D2 của Excel là:

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

Trong đó:

• A2: chính là đoạn text cần tách ký tự ra khỏi bởi hàm left
• SEARCH(“@”,A2): là việc tìm vị trí ký tự @ trong ô A2
• SEARCH(“@”,A2)-1: số ký tự cần lấy của hàm Left tương ứng là phần bên trái của ký tự @

Khi chúng ta muốn tách ký tự dựa theo 1 ký tự đặc trưng mà theo đó có thể nhận biết được, chúng ta dùng hàm Search để thực hiện tìm vị trí ký tự đó. Sau đó có thể dựa theo phần ký tự cần lấy nằm ở vị trí nào thì dùng hàm chính thực hiện phân tách ký tự tương ứng.

2. Hàm Left kết hợp với hàm Len

Khi chúng ta muốn chuyển toàn bộ tất cả dữ liệu giá trị trong 1 ô về dạng text thì làm thế nào? Câu trả lời ở đây rất đơn giản: Dùng hàm LEFT với số ký tự mà chúng ta cần lấy bằng đúng tổng số ký tự đoạn text. Kết quả hàm LEFT cho ra sẽ luôn là dữ liệu dạng Text.

Bước 1: Sử dụng hàm LEN để thực hiện đo độ dài số ký tự trong ô

Bước 2: Dùng hàm LEFT, trong đó phần trường num_char chính là kết quả của hàm LEN

Hãy xem ví dụ tương tự dưới đây

Hàm Left kết hợp với hàm Len

Tại cột A là vị trí chứa giá trị là các con số (có thể kiểm tra điều này bằng hàm IsNumber ra kết quả TRUE tại cột B)

Tại cột C ta sử dụng hàm LEFT kết hợp với hàm LEN để thực hiện chuyển các con số ở cột A về dạng TEXT (kiểm tra kết quả bằng hàm IsText ra kết quả TRUE tại cột D)

VI. Kết

Trên đây là những ứng dụng và lỗi thường gặp của hàm LEFT khi đứng một mình lẫn khi kết hợp với một số hàm khác trong việc làm Excel. Bạn có biết cách kết hợp nào thú vị và hữu ích khác nữa không? Hãy trao đổi với 123job.vn nhé.