1. Hàm TEXTBEFORE trong Excel

Hàm TEXTBEFORE trong Excel được thiết kế đặc biệt để trả về văn bản xuất hiện trước một ký tự hoặc chuỗi con đã cho (dấu phân cách). Trong trường hợp dấu phân cách xuất hiện trong ô nhiều lần, hàm có thể trả về văn bản trước một lần xuất hiện cụ thể. Nếu không tìm thấy dấu phân cách, bạn có thể trả về văn bản của riêng mình hoặc chuỗi gốc.

Cú pháp của hàm TEXTBEFORE có 6 đối số, trong đó chỉ có hai đối số đầu tiên là bắt buộc.

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

text (bắt buộc) - văn bản gốc để trích xuất. Có thể được cung cấp dưới dạng tham chiếu chuỗi hoặc ô.

delimiter (bắt buộc) - một ký tự hoặc chuỗi con phân định văn bản.

instance_num (tùy chọn) - phiên bản của dấu phân cách trước đó để trích xuất văn bản. Mặc định là 1. Số âm bắt đầu tìm kiếm từ cuối văn bản gốc.

match_mode (tùy chọn) - xác định phân biệt chữ hoa chữ thường của dấu phân cách. Được bật theo mặc định.

- 0 (mặc định) - phân biệt chữ hoa, chữ thường.

- 1 – không phân biệt chữ hoa, chữ thường.

match_end (tùy chọn) - coi phần cuối của văn bản là dấu phân cách. Bị tắt theo mặc định.

- 0 (mặc định) - khớp chính xác dấu phân cách như được chỉ định trong công thức.

- 1 - khớp dấu phân cách với phần cuối của văn bản. Thực tế, điều đó có nghĩa là nếu không tìm thấy dấu phân cách, hàm sẽ trả về văn bản gốc.

if_not_found (tùy chọn) - giá trị trả về nếu không tìm thấy dấu phân cách. Nếu không được đặt, lỗi #N/A sẽ được trả về.

2. Tính khả dụng của hàm TEXTBEFORE

Hiện tại, hàm TEXTBEFORE chỉ sẵn dùng trong Excel for Microsoft 365, Excel 365 for Mac và Excel dành cho web.

3. Công thức Excel để trích xuất văn bản trước ký tự

Để bắt đầu, hãy tìm hiểu cách tạo công thức TEXTBEFORE ở dạng đơn giản nhất.

Giả sử bạn có một danh sách tên đầy đủ trong cột A và muốn trích xuất tên xuất hiện trước dấu phẩy.

Điều đó có thể được thực hiện với công thức cơ bản này:

Trong đó A2 là chuỗi văn bản gốc và dấu phẩy (",") là dấu phân cách.

4. Trích xuất văn bản trước khoảng trắng đầu tiên trong Excel

Để lấy văn bản trước dấu cách trong chuỗi, chỉ cần sử dụng ký tự khoảng trắng cho dấu phân cách (" ").

Vì đối số instance_num được đặt thành 1 theo mặc định, nên công thức sẽ trả về văn bản xuất hiện trước khoảng trắng đầu tiên.

Mẹo: Để trích xuất văn bản trước khoảng trắng thứ N, hãy xác định số lần xuất hiện bằng cách sử dụng đối số instance_num thứ 3 (tùy chọn).

5. Trích xuất văn bản trước dấu phân cách thứ N

Để trích xuất văn bản xuất hiện trước lần xuất hiện thứ n của dấu phân cách, hãy cung cấp số cho tham số instance_num.

Ví dụ: để nhận văn bản trước lần xuất hiện thứ hai của dấu phẩy, công thức là:

Mẹo: Nếu không tìm thấy lần xuất hiện đã chỉ định, TEXTBEFORE sẽ trả về lỗi #N/A. Thay vào đó, bạn có thể định cấu hình đối số if_not_found để trả về văn bản bạn chỉ định hoặc đặt match_end thành 1 để xuất văn bản gốc.

6. Trả về văn bản trước lần xuất hiện cuối cùng của dấu phân cách

Để trả về văn bản trước lần xuất hiện cuối cùng của ký tự được chỉ định, hãy đặt một giá trị âm trong đối số instance_num.

Ví dụ: để trả về văn bản trước dấu phẩy cuối cùng trong A2, công thức là:

Để trích xuất văn bản trước cả dấu phẩy cuối cùng, hãy đặt instance_num thành -2:

7. Trích xuất văn bản trước chuỗi con

Đối số dấu phân cách của TEXTBEFORE có thể là một chuỗi con, tức là một chuỗi ký tự, bổ sung thêm nhiều giá trị hơn cho hàm.

Giả sử bạn có một danh sách tên nhân viên và vị trí của họ trong một cột, được phân tách bằng dấu gạch nối. Vấn đề là một số tên được gạch nối. Để tránh tách các tên như vậy, chúng tôi sử dụng một chuỗi con bao gồm khoảng trắng và dấu gạch ngang (" -") cho dấu phân cách:

8. Trích xuất văn bản trước nhiều dấu phân cách

Để xử lý các biến thể khác nhau của dấu phân cách, bạn có thể sử dụng hằng số mảng như {"a","b","c"} trong công thức TEXTBEFORE của mình, trong đó a, b và c đại diện cho các dấu phân cách khác nhau.

Ví dụ: nếu dấu phân cách là dấu phẩy hoặc dấu gạch nối, có hoặc không có ký tự khoảng trắng phía trước, thì bạn có thể xử lý chính xác cả bốn biến thể bằng cách cung cấp hằng số mảng {","," ,","-"," -" } cho dấu phân cách:

9. Nếu không tìm thấy dấu phân cách, hãy trả về văn bản tùy chỉnh

Nếu không tìm thấy dấu phân cách đã chỉ định, hàm TEXTBEFORE sẽ trả về lỗi #N/A theo mặc định. Thay vào đó, để trả về một giá trị tùy chỉnh, hãy định cấu hình đối số if_not_found (đối số cuối cùng).

Ví dụ: nếu không có một dấu phẩy nào xuất hiện trong văn bản nguồn (A2), thì công thức cơ bản này sẽ đưa ra lỗi #N/A:

Để loại bỏ lỗi, hãy nhập bất kỳ văn bản nào bạn muốn vào đối số cuối cùng, ví dụ: "Không tìm thấy":

Để không trả lại gì, tức là một ô trống, hãy sử dụng một chuỗi trống cho if_not_found:

10. Nếu không tìm thấy dấu phân cách, hãy trả về văn bản gốc

Trong một số trường hợp, bạn nên trả về văn bản gốc nếu hàm TEXTBEFORE không khớp với dấu phân cách trong chuỗi. Đối với điều này, chỉ cần đặt đối số match_end (đối số thứ 5) thành 1. Điều này sẽ cho công thức coi phần cuối của văn bản là dấu phân cách.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, công thức hiện trả về tên nhân viên ngay cả khi dấu phân cách (là dấu phẩy) không có trong một ô.

11. Làm cho dấu phân cách phân biệt chữ hoa chữ thường hoặc chữ hoa chữ thường

Theo mặc định, hàm TEXTBEFORE của Excel phân biệt chữ hoa chữ thường, nghĩa là nó coi các dấu phân cách chữ thường và chữ hoa là các dấu phân cách khác nhau. Để tắt phân biệt chữ hoa chữ thường, hãy đặt đối số match_mode (đối số thứ 4) thành 1 hoặc TRUE.

Ví dụ: công thức dưới đây chỉ chấp nhận chữ thường "x" làm dấu phân cách:

Trong khi một công thức tương tự với match_mode được đặt thành 1, sẽ nhận dạng lại cả "x" và "X" làm dấu phân cách:

12. Hàm TEXTBEFORE không hoạt động?

Nếu hàm TEXTBEFORE không hoạt động như mong đợi hoặc không có sẵn trong Excel của bạn, thì đó có thể là một trong những lý do sau.

Nếu TEXTBEFORE không tồn tại trong Excel của bạn, vui lòng kiểm tra danh sách các phiên bản được hỗ trợ. (Office 365 cho Mac và Window, Office trên Web)

Nếu TEXTBEFORE trả về lỗi #N/A, thì:

- Dấu phân cách không tồn tại trong văn bản nguồn. Để xử lý lỗi, hãy định cấu hình đối số if_not_found như được giải thích trong ví dụ này.

- Giá trị instance_num lớn hơn số lần xuất hiện của dấu phân cách trong văn bản.

Nếu TEXTBEFORE trả về lỗi #VALUE! lỗi, sau đó:

- Đối số instance_num được đặt thành 0.

- Giá trị instance_num lớn hơn tổng độ dài của văn bản.

Bạn có thể download File đính kèm tại đây: