Đánh giá lequocthai.com:
Hàm TEXTBEFORE là một bổ sung mới cho các hàm trong Excel và chỉ có sẵn cho người dùng Microsoft 365.
Cú pháp
Cú pháp của hàm TEXTBEFORE bao gồm sáu đối số, như sau:
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])Đối số
Trong sáu đối số, chỉ có hai là bắt buộc. Chi tiết và chức năng của mỗi đối số được mô tả dưới đây.
- text – Đối số bắt buộc đầu tiên. Giá trị đầu vào của text là chuỗi gốc mà chúng ta muốn trích xuất. Nó có thể là một giá trị trực tiếp trong dấu ngoặc kép hoặc là một tham chiếu ô chứa văn bản.
- delimiter – Đối số bắt buộc thứ hai, chấp nhận một ký tự hoặc chuỗi mà chúng ta muốn dùng làm dấu phân cách. Giá trị của delimiter có thể là một giá trị trực tiếp trong dấu ngoặc kép hoặc là một tham chiếu ô.
- instance_num – Đối số tùy chọn, mặc định là 1. instance_num chỉ ra lần xuất hiện thứ n của dấu phân cách mà chúng ta muốn lấy đoạn văn bản phía trước. Nếu giá trị của instance_num là số âm, hàm TEXTBEFORE sẽ tìm dấu phân cách từ phải sang trái.
- match_mode – Đối số tùy chọn, nhận giá trị boolean 1 hoặc 0. Mặc định là 0, khiến hàm phân biệt chữ hoa và chữ thường. Nếu đặt thành 1, hàm sẽ thực hiện tìm kiếm không phân biệt chữ hoa chữ thường.
- match_end – Đối số tùy chọn, cũng nhận giá trị boolean 1 hoặc 0. Mặc định là 0. Khi đối số match_end được đặt thành 1, hàm sẽ xem cuối chuỗi văn bản như một dấu phân cách.
- if_not_found – Đối số cuối cùng, hữu ích khi dấu phân cách không tồn tại trong text. Mặc định là #N/A. if_not_found cho phép chúng ta chỉ định giá trị muốn trả về khi không tìm thấy dấu phân cách; có thể là một giá trị trực tiếp trong dấu ngoặc kép hoặc một tham chiếu ô.
Đặc điểm quan trọng của hàm TEXTBEFORE
- Vì TEXTBEFORE là hàm xử lý văn bản, giá trị trả về luôn ở dạng văn bản.
- Nếu đối số text để trống, hàm sẽ trả về lỗi #N/A.
- instance_num chỉ chấp nhận giá trị nguyên; nếu nhập số thập phân, phần nguyên sẽ được dùng.
- Khi instance_num bằng 0 hoặc lớn hơn độ dài của text, hàm TEXTBEFORE sẽ trả về lỗi #VALUE!.
- Nếu giá trị instance_num vượt quá số lần xuất hiện của dấu phân cách, hàm trả về lỗi #N/A.
Ví dụ về hàm TEXTBEFORE
Như hàm TEXTBEFORE có nhiều đối số, chúng ta sẽ hiểu cách sử dụng từng đối số qua các ví dụ dưới đây.
Ví dụ 1 – Sử dụng cơ bản hàm TEXTBEFORE
Trong ví dụ này, chúng tôi lấy một số dữ liệu mẫu ở dạng văn bản và số để minh họa chức năng cơ bản của TEXTBEFORE.
- Ví dụ đầu tiên là một địa chỉ email. Chúng ta muốn lấy phần tên người dùng xuất hiện trước ký tự “@”. Đặt “@” làm delimiter, kết quả trong ô C3 là “mark”.
- Ví dụ tiếp theo là một giá trị số trong ô B4. Chúng ta muốn lấy các ký tự trước số 5, vì vậy delimiter là “5”. Kết quả trả về là “1234” và được căn trái.
- Trong trường hợp cuối cùng, chúng ta muốn lấy phần văn bản xuất hiện trước dấu chấm cuối cùng. Vì có nhiều dấu chấm, chúng ta dùng đối số thứ ba instance_num và đặt giá trị 3, hàm sẽ trả về đoạn trước dấu chấm thứ ba (là dấu chấm cuối cùng).
Sau khi nắm vững cách dùng cơ bản, chúng ta sẽ khám phá các ứng dụng mở rộng qua các ví dụ tiếp theo.
Ví dụ 2 – Trích xuất tên người dùng bằng TEXTBEFORE
HR đã cung cấp danh sách các giải thưởng công ty năm ngoái, nhưng thông tin chưa được định dạng thành danh sách. Để gửi danh sách người nhận cho bộ phận chứng nhận, chúng ta cần tách tên ra khỏi chuỗi.
Mỗi tên đều theo sau từ “won”, vì vậy chúng ta có thể dùng “won” làm delimiter và lấy phần trước đó:
=TEXTBEFORE(B3,"won")
Cũng có thể dùng dấu cách làm delimiter, nhưng vì có nhiều dấu cách, chúng ta dùng instance_num = 2 để lấy đoạn trước dấu cách thứ hai:
=TEXTBEFORE(B3," ",2)
Bằng một trong hai cách, chúng ta có thể xuất danh sách nhân viên cần thiết.
Ví dụ 3 – Trích xuất tên phim bằng TEXTBEFORE
Sau lễ trao giải Golden Globe, chúng tôi tải danh sách các bộ phim thắng giải để xem vào cuối tuần. Muốn tách tên phim ra cột riêng, chúng ta sẽ dùng TEXTBEFORE.
Trong mỗi dòng, ký tự hoặc chuỗi ngăn cách tên phim có thể khác nhau (dấu gạch ngang “-“, dấu hai chấm “:” hoặc từ “won”). Chúng ta có thể truyền nhiều dấu phân cách dưới dạng mảng:
=TEXTBEFORE(B3,{"-",":","won"})
Kết quả là danh sách tên phim đã được tách ra.
Ví dụ 3.1 – Hiểu match_mode trong TEXTBEFORE
Nếu dữ liệu phim có kiểu chữ không đồng nhất, hàm sẽ không tìm thấy dấu phân cách khi phân biệt chữ hoa/chữ thường. Ví dụ, trong ô B5 dấu phân cách là “Won” (chữ hoa), còn chúng ta đặt “won” (chữ thường). Khi đó hàm trả về #N/A.
Để bỏ qua phân biệt chữ hoa/chữ thường, đặt match_mode = 1:
=TEXTBEFORE(B3,{"-",":","won"},,1)
Ví dụ 4 – Đối số instance_num âm trong TEXTBEFORE
Chúng tôi có một danh sách các câu hỏi toán kèm đáp án, mỗi câu hỏi kết thúc bằng dấu “=” và sau đó là đáp án. Để chỉ lấy câu hỏi, dùng dấu “=” làm delimiter.
Công thức cơ bản:
=TEXTBEFORE(B3,"=")
Nhưng mỗi câu có nhiều dấu “=”, dấu cuối cùng mới là dấu ngăn cách đáp án. Vì vậy chúng ta dùng instance_num âm để tìm dấu “=” từ phải sang trái:
=TEXTBEFORE(B3,"=",-1)
Ví dụ 5 – Tính tổng bằng TEXTBEFORE
Một công ty chuyển phát đã gửi chi tiết số lượng hàng. Để tính tổng số đơn vị, chúng ta trích xuất số trước dấu cách đầu tiên và cộng lại để tính tổng.
Công thức để lấy số:
=TEXTBEFORE(B3," ")
Vì TEXTBEFORE trả về dạng văn bản, các giá trị trong cột C được căn trái. Để chuyển sang số, dùng hàm VALUE:
=VALUE(TEXTBEFORE(B3," "))
Sau khi chuyển đổi, có thể tính tổng:
=SUM(VALUE(TEXTBEFORE(B3:B8," ")))
Ví dụ 6 – Xử lý trường hợp không có dấu phân cách
Danh sách những người giàu nhất thế giới (Forbes) gồm tên và quốc gia, ngăn cách bằng dấu phẩy. Khi một số dòng thiếu dấu phẩy, hàm sẽ trả về #N/A theo mặc định.
Công thức cơ bản:
=TEXTBEFORE(B3,",")
Để trả về nguyên văn bản khi không tìm thấy dấu phẩy, sử dụng đối số if_not_found:
=TEXTBEFORE(B3,",",,,,B3)
Hoặc dùng match_end = 1, khiến hàm xem cuối chuỗi như một dấu phẩy khi không tìm thấy dấu phân cách:
=TEXTBEFORE(B3, ",", , ,1)
TEXTBEFORE vs TEXTAFTER
Giống như TEXTBEFORE lấy phần trước dấu phân cách, hàm TEXTAFTER lấy phần sau dấu phân cách. Ví dụ với danh sách họ và tên, dùng TEXTBEFORE để lấy họ (trước dấu cách) và TEXTAFTER để lấy tên (sau dấu cách):
=TEXTBEFORE(B4," ")
=TEXTAFTER(B4," ")</code></pre>
Nếu không dùng Microsoft 365, bạn vẫn có thể kết hợp các hàm RIGHT, LEFT, SEARCH và LEN để đạt được kết quả tương tự.
Hãy thực hành hàm TEXTBEFORE để khám phá những ứng dụng mới và cách sử dụng thú vị. Khi bạn đã thành thạo, chúng tôi sẽ giới thiệu thêm một hàm Excel khác để mở rộng bộ công cụ của bạn.
