Trong quá trình thao tác xử lý văn bản trên Microsoft Excel, có những đoạn văn bản mà bạn cần lấy kí tự ở đầu, kí tự ở giữa hay kí tự ở cuối thì các LEN, MID, SEARCH, LEFT, RIGHT thường được sử dụng phổ biến . Tuy nhiên làm cách nào để có thể phối hợp cá hàm này lại và sử dụng một cách tối ưu? Trong bài viết dưới đây, Đào tạo tin học sẽ hướng dẫn các bạn cách sử dụng các hàm lấy ký tự trong Excel để lấy ký tự theo mong muốn và yêu cầu.
Hàm lấy ký tự trong Excel ở đầu văn bản
Hàm LEFT trong Excel lấy ký tự ở đầu văn bản
– Ví dụ 1: Để lấy được ký tự ở đầu văn bản trong ô Excel, bạn có thể thực hiện bằng hàm LEFT để lấy ký tự này từ đoạn văn bạn. Trong ví dụ như hình ảnh bên dưới, để lấy 2 kí tự đầu của dãy các chuỗi văn bản bạn có thể sử dụng hàm LEFT bàng cách nhập công thức hàm lấy ký tự trong Excel tại ô: =LEFT[B2,2]
Kết hợp hàm LEN, SEARCH, LEFT trong Excel lấy ký tự đầu văn bản
– Ví dụ 2: Sau đây là ví dụ kết hợp hàm LEN, SEARCH và hàm LEFT để lấy kí tự bên trái của đoạn văn bản. Để lấy ký tự tên sản phẩm của chuỗi văn bản gồm Tên sản phẩm + Ngày tháng năm nhập sản phẩm + Nội dung. Làm cách nào để bạn có thể lấy được các tên sản phẩm ra khỏi chuỗi văn bản khi không xác định trước được số ký tự bên trái cần lấy.
– Đầu tiên, bạn dùng hàm SEARCH để tìm vị trí bắt đầu cần bỏ ký tự
Hàm lấy ký tự trong Excel ở giữa văn bản
Để lấy kí tự ở giữa ra khỏi chuỗi chuỗi văn bản, chắc hẳn bạn sẽ nghĩ ngay đến hàm MID trong excel. Tuy nhiên, hàm này chỉ là hàm để bạn xác định được các ký tự ở giữa bắt đầu từ số kí tự thứ mấy trong chuỗi văn bản. Để loại bỏ được ký tự ở giữa ra khỏi chuỗi văn bản, bạn cần thực hiện các bước sau đây.
Trong ví dụ bên dưới đây sẽ yêu cầu các bạn cần tách ngày tháng năm sản phẩm ra chuỗi văn bản gồm Tên sản phẩm + ngày tháng năm nhập sản phẩm + nội dung.
– Đầu tiên, bạn xác định đoạn text ở giữa cần bỏ kí tự là gồm 8 ký tự gồm ngày tháng năm. Tại ô hàm SEARCH để tìm vị trí bắt đầu, bạn nhập công thức hàm =SEARCH[“2021”,B2]
Tìm vị trí kết thúc với hàm SEARCH như sau =SEARCH[“2021”,B2].
– Bước 2: Xác định ký từ đầu tiên cần lấy từ bên trái đến là SEARCH[“2021”,B2] và ký tự ở phần bên phải từ ký tự đầu tiên SEARCH[“2021”,B2]+8-1. Xác định phần ký tự cần bỏ đi là 8 ký tự gồm ngày tháng năm nhập sản phẩm.Cuối cùng, bạn nhập công thức hàm LEFT để thực hiện tách ký tự ở giữa ra khỏi chuỗi văn bản.
Vậy là bạn đã tách được các ký tự ở giữa ra khỏi chuỗi văn bản rồi.
Nếu không chèn thêm các cột để sử dụng hàm SEARCH, bạn cũng có thể sử dụng công thức lồng ghép như hình ảnh bên dưới nhé!
Hàm lấy ký tự trong Excel ở cuối văn bản
– Ví dụ: Để loại bỏ các ký tự trước dấu chấm để xem các đuôi tên miền website. Tuy nhiên, phần lý tự trước dâu chấm không có một con số cố định ký tự, vậy làm cách nào để bạn có thể loại các ký tự trước dấu chấm một cách nhanh chóng mà không mất thời gian làm nhiều lần? Gợi ý là bạn có thể sử dụng kết hợp hàm SEARCH và hàm RIGHT với các bước thực hiện đơn giản như sau:
– Đầu tiên, bạn dùng hàm SEARCH trong Excel để tìm kiếm dấu chấm trong chuỗi văn bản.
Bạn chèn thêm một cột hàm SEARCH trong Excel và nhập công thức vào ô: =SEARCH[“.”,B2] để tìm dấu chấm trong chuỗi văn bản của các ô. Vì đây là text nên điều kiện cần được đặt trong dấu ngoặc kép. Kết quả trả về của hàm SEARCH là số vị trí của dấu chấm xuất hiện trong chuỗi văn bản tại ô B2.
– Bước 2: Bạn sử dụng hàm RIGHT để lấy số ký tự sau dấu chấm bằng cách tổng số ký tự của chuỗi text trừ đi cho tổng số ký tự trước dấu chấm.
Sau đó bạn tiếp tục chèn thêm một cột hàm RIGHT để lấy ký tự sau dấu chấm bằng cách nhập công thức: =RIGHT[B2,LEN[B2]-C2. Ở bước này hàm LEN dùng để xác định tổng số ký tự trong chuỗi văn bản ô B2. LEN[B2]-C2 là số ký tự ở bên phải còn lại chuỗi văn bản ô B2 sau khi trừ đi. Kết quả cuối cùng công thức hàm RIGHT trong excel – hàm lấy ký tự trong Excel sẽ hiển thị số ký tự cần lấy.
>> Xem thêm Hướng dẫn sử dụng hàm RIGHT và RIGHTB trong Excel
Hy vọng với bài viết này từ Đào tạo tin học sẽ giúp bạn biết cách sử dụng hàm lấy ký tự trong excel, ứng dụng vào công việc và học tập một cách hiệu quả nhé. Nếu trong quá trình thực hiện, các bạn có gặp bất cứ khó khăn nào có thể để lại bình luận bên dưới để được hỗ trợ hướng dẫn. Bên cạnh đó để không bỏ lỡ những mẹo hay trong tin học văn phòng, bạn có thể theo dõi các trang mạng xã hội của Đào tạo tin học ngay nhé!
Chúc các bạn áp dụng thành công!
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.
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:
=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:
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ương đương với số âm:
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.
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…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản đến nâng cao dành cho người đi làm