Trong quá trình xử lý văn bản hay ký tự text trong Excel, chúng ta thường biết tới các hàm như LEFT, MID, RIGHT, LEN, SEARCH… Nhưng nếu chỉ biết từng hàm riêng lẻ thì khá đơn giản và khó để biết hết sức mạnh của các hàm này. Vậy thì trong bài viết này, hẳn bạn sẽ bất ngờ khi biết các hàm này kết hợp với nhau tạo ra hiệu quả đáng kinh ngạc thế nào. Nguyên lý hoạt động của hàm lấy ký tự. Cùng tìm hiểu ngay nào.

Công thức lọc bỏ ký tự ở đầu đoạn văn bản

Ví dụ chúng ta cần lọc bỏ các ký tự từ dấu chấm đầu tiên trở về trước (số ký tự từ bên trái tới dấu chấm đầu tiên) để xem đuôi của các trang web là gì:

Phần ký tự bên trái trước dấu chấm đầu tiên không hề cố định là bao nhiêu ký tự phải không? Vậy làm sao để thực hiện yêu cầu trên?

Bí quyết nằm ở dấu chấm đầu tiên. Chúng ta có thể xác định được dấu chấm này bằng hàm SEARCH.

Phần kết quả cần lấy là số ký tự phía bên phải của đoạn text, sau vị trí dấu chấm đầu tiên trở đi. Do đó:

• Sử dụng hàm SEARCH tìm vị trí dấu chấm đầu tiên
• Sử dụng hàm RIGHT với số ký tự cần lấy là Tổng số ký tự đoạn text trừ đi Vị trí dấu chấm đầu tiên

Hãy xem kết quả nào:

• Thứ 1: Hàm SEARCH(“.”,A2) dùng để tìm vị trí dấu chấm đầu tiên trong ô A2. Dấu chấm là 1 ký tự text nên cần đặt trong cặp dấu nháy kép. Kết quả của hàm SEARCH là số vị trí thứ mấy của dấu chấm đầu tiên tìm được trong đoạn text ở ô A2
• Thứ 2: Hàm LEN(A2) để xác định tổng số ký tự có trong ô A2.
• Thứ 3: LEN(A2)-SEARCH(“.”,A2) sẽ ra kết quả là số ký tự còn lại nằm ở bên phải dấu chấm đầu tiên, không còn tính những ký tự bên trái dấu chấm đầu tiên nữa. Đây chính là số ký tự cần tách trong ô A2 bằng hàm RIGHT.

Do đó viết hàm RIGHT(A2,LEN(A2)-SEARCH(“.”,A2)) sẽ cho chúng ta kết quả đúng.

Đây là 1 trong những công thức tách lọc phần ký tự năm bên phải đoạn text thường xuyên được sử dụng (hay nói cách khác là xóa bỏ phần bên trái của đoạn text)

Ngoài ra các bạn có thể thay hàm SEARCH bằng một con số cụ thể trong trường hợp luôn luôn xóa bỏ bao nhiêu ký tự nằm bên trái đoạn text.

Tham khảo thêm ứng dụng của hàm SEARCH qua bài viết:

Hướng dẫn cách ứng dụng hàm SEARCH và COUNTIFS để thống kê tình trạng làm việc

Cách lọc bỏ ký tự ở bên phải của đoạn văn bản

Cách này ngược lại với lọc ký tự bên trái, cũng là sự phối hợp của các hàm LEFT, LEN, SEARCH nhưng phức tạp hơn 1 chút. Hãy xem ví dụ sau đây nhé:

Ví dụ chúng ta có các ID gồm thông tin Tên sản phẩm & Năm & Tháng & Ngày & Nội dung. Yêu cầu là cần tách lọc ra các tên sản phẩm trong từng ID.

Có thể thấy số ký tự cần tách không cố định là 3 hay 4 ký tự, mà thay đổi theo từng ID. Căn cứ ở đây là đoạn ký tự kéo dài tới trước con số 2018 (hay số 2 đầu tiên trong chuỗi)

Độ dài đoạn text cần lọc = Phần phía trước vị trí số 2018 = SEARCH(“2018”,A2)-1

Kết quả thu được như sau:

Cách lọc bỏ ký tự ở giữa của đoạn văn bản

Nhắc đến ký tự ở giữa đoạn văn bản, chúng ta thường nghĩ tới hàm MID. Nhưng hàm này chỉ đơn giản là “Xác định ký tự nằm giữa trong chuỗi text”. Vậy nên việc lọc bỏ những ký tự này bao gồm bạn phải cắt đoạn text gốc thành 3 phần, loại bỏ phần ở giữa đi, sau đó nối đoạn đầu và đoạn cuối lại với nhau. Vấn đề là phải xác định Đoạn đầu và Đoạn cuối như thế nào?

Hãy xem ví dụ sau:

Yêu cầu là chúng ta cần lấy nội dung ID nhưng bỏ đi các con số thể hiện thời gian (năm + tháng + ngày), tổng cộng là 8 ký tự.

Vậy trong trường hợp này, chúng ta sẽ làm như sau:

• Phần đoạn text cần bỏ đi: 8 ký tự. Bắt đầu từ vị trí hàm SEARCH(“2018”,A2), kết thúc với vị trí SEARCH(“2018”,A2)+8-1
• Phần bên trái: Từ ký từ đầu tiên bên trái tới trước vị trí SEARCH(“2018”,A2)
• Phần bên phải: Từ ký tự đầu tiên bên phải tới sau vị trí SEARCH(“2018”,A2)+8-1

Kết quả như sau:

Nếu không dùng 2 cột C và D, chúng ta có thể lồng ghép công thức tại các cột này vào trong công thức tại ô B2 như sau:

Nhìn khá dài phải không nào. Nhưng vì chúng ta đã hiểu bản chất nên cũng không khó để viết ra công thức này.

Hy vọng rằng qua bài viết này các bạn có thể thấy được việc ứng dụng các hàm trong Excel diễn ra như thế nào. Thực tế công việc không phải đơn giản như khi chúng ta học, mà nó cần được phân tích, làm rõ tính logic trước, sau đó là quá trình kết hợp các hàm đã học lại với nhau để xử lý từng bước, khi đó mới cho chúng ta kết quả đúng.

Các bạn có thể tham khảo thêm một số bài viết cùng chủ đề sau đây:

Hướng dẫn cách sử dụng hàm VLOOKUP kết hợp hàm LEFT trong Excel

Hướng dẫn cách sử dụng hàm vlookup kết hợp hàm mid

Hướng dẫn cách sử dụng hàm Sumifs kết hợp hàm vlookup trong Excel