Hướng dẫn đầy đủ về cách tìm và trích xuất văn bản từ chuỗi giữa hai ký tự hoặc từ trong Excel và Google Sheets.
Cách trích xuất văn bản giữa hai ký tự trong Excel
Để trích xuất văn bản giữa hai ký tự khác nhau, bạn có thể sử dụng công thức chung sau:
Ví dụ: để lấy văn bản giữa các dấu ngoặc đơn từ chuỗi trong A2, công thức là:
=MID(A2, SEARCH(“(“, A2)+1, SEARCH(“)”, A2) – SEARCH(“(“, A2) -1)
Theo cách tương tự, bạn có thể trích xuất văn bản giữa các dấu ngoặc nhọn, dấu ngoặc vuông, dấu ngoặc nhọn, v.v.
Ví dụ:
=MID(A2, SEARCH(“(“, A2)+1, SEARCH(“)”, A2) – SEARCH(“(“, A2) -1) *1
Vui lòng lưu ý căn chỉnh bên phải mặc định của các giá trị được trích xuất trong các ô điển hình cho số:
Công thức này hoạt động như thế nào
Cơ sở của công thức này là hàm MID lấy một số ký tự nhất định từ một chuỗi, bắt đầu từ một vị trí cụ thể:
MID(văn bản, số bắt đầu, số_ký tự)
Văn bản là ô chứa chuỗi gốc (A2).
Vị trí bắt đầu ( start_num ) là ký tự ngay sau dấu ngoặc đơn mở. Vì vậy, bạn tìm vị trí của “(” bằng hàm SEARCH và thêm 1 vào đó:
SEARCH(“(“, A2) +1
Để tìm ra có bao nhiêu ký tự cần trích xuất ( num_chars ), bạn xác định vị trí của dấu ngoặc đơn đóng và trừ đi vị trí của dấu ngoặc đơn mở từ nó. Ngoài ra, bạn trừ 1 để bỏ dấu ngoặc đơn thứ hai:
SEARCH(“)”, A2) – SEARCH(“(“, A2) -1
Có tất cả các chi tiết cần thiết, hàm MID mang đến cho bạn chính xác những gì bạn muốn – văn bản giữa các dấu ngoặc đơn trong trường hợp sau:
MID(A2, 19, 2)
Vì MID là một hàm văn bản nên nó luôn tạo ra một chuỗi, ngay cả khi việc trích xuất chỉ bao gồm các số. Để lấy một số làm kết quả cuối cùng, chúng ta nhân kết quả đầu ra của MID với một hoặc thêm số 0 vào đó. Nếu bạn đang trích xuất văn bản, thao tác này không cần thiết.
Trích xuất văn bản giữa hai chuỗi/từ trong Excel
Để kéo văn bản giữa hai chuỗi hoặc từ, công thức khá giống với công thức đã thảo luận ở trên. Chỉ cần một vài điều chỉnh khác nhau: để xóa các từ phân cách khỏi kết quả cuối cùng, bạn cộng và trừ độ dài của các từ được hàm LEN trả về :
MID( cell , TÌM KIẾM( word1 , cell ) + LEN( word1 ), SEARCH( word2 , cell ) – TÌM KIẾM( word1 , cell ) – LEN( word1 ))
Ví dụ: để trích xuất các chuỗi con giữa các từ “bắt đầu” và “kết thúc”, công thức là:
=IFERROR(MID(A2, SEARCH(“start “, A2) + LEN(“start “), SEARCH(” end”, A2) – SEARCH(“start “, A2) – LEN(“start “)), “”)
Nhận văn bản giữa hai phiên bản của cùng một ký tự trong Excel
Để trích xuất văn bản từ một chuỗi giữa hai lần xuất hiện của cùng một ký tự, công thức chung là:
MID( cell , TÌM KIẾM( char , cell ) +1, TÌM KIẾM ( char , cell , TÌM KIẾM ( char , cell ) +1) – TÌM KIẾM ( char , cell ) -1)
Ví dụ: để trích xuất văn bản giữa các dấu ngoặc kép từ chuỗi trong A2, bạn nhập công thức này vào B2:
=MID(A2, SEARCH(“”””, A2) +1, SEARCH(“”””, A2, SEARCH(“”””,A2) +1) – SEARCH(“”””, A2) -1)
Hãy chú ý đến cách bạn tìm kiếm dấu ngoặc kép trong Excel. Giữa các dấu ngoặc kép bên ngoài, một bộ dấu ngoặc kép khác được nhập vào. Trích dẫn đầu tiên được sử dụng để thoát khỏi ý nghĩa đặc biệt của trích dẫn thứ hai sao cho “” ở giữa các trích dẫn ngoài cùng là viết tắt của một trích dẫn kép.
Một cách khác để cung cấp dấu ngoặc kép (“) cho công thức Excel là sử dụng hàm CHAR với mã số 34.
=MID(A2, SEARCH(CHAR(34), A2) +1, SEARCH(CHAR(34), A2, SEARCH(CHAR(34),A2) +1) – SEARCH(CHAR(34), A2) -1)
Công thức này hoạt động như thế nào
Hai đối số đầu tiên của công thức MID này không đặt ra câu hỏi nào:
-
A2 là chuỗi văn bản cần tìm kiếm và
-
SEARCH(“”””, A2) +1 là số bắt đầu, tức là vị trí của trích dẫn đầu tiên +1.
Phần khó nhất là tính số ký tự cần trích xuất (num_chars):
Đầu tiên, chúng ta tìm vị trí của trích dẫn thứ hai bằng cách lồng một hàm TÌM KIẾM vào trong một hàm khác .
SEARCH(“”””, A2, SEARCH(“”””,A2) +1)
Từ vị trí của trích dẫn thứ 2 (trong A2 là 27), bạn trừ vị trí của trích dẫn thứ 1 (trong A2 là 10), rồi trừ 1 để loại trừ chính trích dẫn đó khỏi kết quả:
SEARCH(“”””, A2, SEARCH(“”””,A2) +1) – SEARCH(“”””, A2) -1
Công thức trên trả về 16, đây là mảnh ghép còn thiếu cuối cùng mà hàm MID cần:
=MID(A2, 10+1, 16)
Nói một cách đơn giản, MID tìm kiếm ô A2 bắt đầu từ ký tự sau dấu ngoặc kép đầu tiên (10+1) và trả về 16 ký tự tiếp theo.
Công thức Excel phân biệt chữ hoa chữ thường để trích xuất văn bản giữa các ký tự
Như bạn có thể biết, trong Microsoft Excel có hai hàm tìm kiếm chuỗi: TÌM KIẾM (không phân biệt chữ hoa chữ thường) và TÌM (phân biệt chữ hoa chữ thường).
Trong trường hợp dấu phân cách là một chữ cái trong trường hợp cụ thể, chỉ cần sử dụng TÌM thay vì TÌM KIẾM. Để minh họa sự khác biệt, hãy so sánh hai công thức dưới đây.
Từ chuỗi trong A2, giả sử bạn muốn trích xuất một số giữa hai chữ cái viết hoa “X”.
Hàm TÌM KIẾM hoạt động không chính xác trong trường hợp này vì nó không phân biệt giữa “x” và “X”:
=MID(A2, SEARCH(“X”, A2) +1, SEARCH(“X”, A2, SEARCH(“X”,A2) +1) – SEARCH(“X”, A2) -1) +0
Kết quả là văn bản giữa “x” được trích xuất chứ không phải giữa “X” đang tìm kiếm:
Trong khi hàm FIND phân biệt chữ hoa chữ thường hoạt động rất tốt:
=MID(A2, FIND(“X”, A2) +1, FIND(“X”, A2, FIND(“X”,A2) +1) – FIND(“X”, A2) -1) +0
Và mang lại chính xác kết quả mà chúng ta cần:
Trích xuất văn bản giữa các ký tự trong Excel 365
Trong Excel 365, bạn có thể lấy văn bản giữa các ký tự dễ dàng hơn bằng cách sử dụng kết hợp các hàm TEXTBEFORE và TEXTAFTER.
TEXTBEFORE(TEXTAFTER( ô , char1 ), char2 )
Ví dụ: để trích xuất văn bản giữa các dấu ngoặc đơn, công thức đơn giản như sau:
=TEXTBEFORE(TEXTAFTER(A2, “(“), “)”)
Công thức này cũng hoạt động tốt trong việc trích xuất văn bản giữa hai lần xuất hiện của cùng một ký tự.
Ví dụ: để lấy văn bản giữa các dấu ngoặc kép, công thức có dạng sau:
=TEXTBEFORE(TEXTAFTER(A2, “”””), “”””)
Theo mặc định, cả hai hàm TEXTAFTER và TEXTBEFORE đều phân biệt chữ hoa chữ thường. Để tắt phân biệt chữ hoa chữ thường, bạn đặt đối số thứ 4 ( match_mode ) thành 1 hoặc TRUE.
Ví dụ: công thức không phân biệt chữ hoa chữ thường bên dưới nhận dạng cả chữ “x” viết thường và chữ hoa “X” làm dấu phân cách:
=TEXTBEFORE(TEXTAFTER(A2, “x “, ,1), ” x”, ,1) +0
Công thức này hoạt động như thế nào
Làm việc từ trong ra ngoài, bạn sử dụng hàm TEXTAFTER để trích xuất văn bản sau dấu ngoặc đơn mở:
TEXTAFTER(A2, “(“)
Và cung cấp chuỗi con được trả về cho TEXTBEFORE , yêu cầu nó tìm dấu ngoặc đơn đóng trong chuỗi con đó và trả về văn bản trước chuỗi đó.
TEXTBEFORE(“Unexpected error)”, “)”)
Nhận văn bản giữa hai ký tự trong Google Sheets
Để tìm văn bản giữa hai ký tự trong Google Trang tính, bạn không cần phải phát minh lại bánh xe 🙂
Các kết hợp MID SEARCH được sử dụng trong Excel cũng hoạt động hoàn hảo trong bảng tính Google.
Ví dụ: để lấy văn bản giữa các dấu ngoặc , công thức là:
=MID(A2, SEARCH(“[“, A2)+1, SEARCH(“]”, A2) – SEARCH(“[“, A2) -1)
Để trích xuất văn bản giữa hai dấu phẩy , đây là công thức sử dụng:
Đó là cách trích xuất văn bản giữa hai ký tự hoặc từ trong bảng tính Microsoft Excel và Google.
