Hướng dẫn lấy dữ liệu có điều kiện trong Excel và Google Trang tính.
Khi làm việc với các chuỗi dài, bạn có thể thường muốn trích xuất một phần cụ thể của chúng để kiểm tra kỹ hơn. Trong Microsoft Excel và Google Trang tính, có một số cách để lấy văn bản giữa hai ký tự hoặc chuỗi con. Trong bài viết này, chúng ta sẽ thảo luận về những cách nhanh nhất và hiệu quả nhất.
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:
MID(cell, SEARCH(char1, cell) + 1, SEARCH(char2, cell) – SEARCH(char1, cell) – 1)
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 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 các số:
Cách thức hoạt động của công thức này
Cơ sở của công thức này là hàm MID kéo một số ký tự nhất định từ một chuỗi, bắt đầu từ một vị trí cụ thể:
MID(text, start_num, num_chars)
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 cách sử dụng hàm SEARCH và thêm 1 vào nó:
SEARCH(“(“, A2) +1
Để tìm ra 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 đóng và trừ đi vị trí của dấu ngoặc mở từ đó. Ngoài ra, bạn trừ đi 1 để bỏ đi dấu ngoặc 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 dấu ngoặc đơn trong trường hợp của chúng tôi:
MID(A2, 19, 2)
Vì MID là một hàm văn bả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ố. Để nhận được một số làm kết quả cuối cùng, chúng tôi nhân đầu ra của MID với một hoặc thêm số 0 vào nó. Nếu bạn đang trích xuất văn bản, thao tác này không bắt buộc.
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 số điều chỉnh khác nhau: để xóa dấu phân cách-các từ khỏi kết quả cuối cùng, bạn thêm và trừ độ dài của chính các từ được trả về bởi hàm LEN :
MID(cell, SEARCH(word1, cell) + LEN(word1), SEARCH(word2, cell) – SEARCH(word1, cell) – LEN(word1))
Ví dụ: để trích xuất chuỗi con giữa các từ “start” và “end”, công thức là:
=IFERROR(MID(A2, SEARCH(“start “, A2) + LEN(“start “), SEARCH(” end”, A2) – SEARCH(“start “, A2) – LEN(“start “)), “”)
Lời khuyên:
- Để tránh khoảng trắng ở đầu và cuối trong kết quả, hãy thêm khoảng trắng sau từ 1 chẳng hạn như “bắt đầu” và trước từ 2 chẳng hạn như “kết thúc”. Ngoài ra, bạn có thể sử dụng hàm TRIM để loại bỏ các khoảng trống thừa.
- Nếu một hoặc cả hai từ đã chỉ định không được tìm thấy trong chuỗi ban đầu, công thức sẽ trả về giá trị lỗi #VALUE! lỗi. Để bắt lỗi đó và thay thế nó bằng một chuỗi trống (“”), hãy sử dụng hàm IFERROR như trong ví dụ trên.
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, SEARCH(char, cell) +1, SEARCH (char, cell, SEARCH (char, cell) +1) – SEARCH (char, cell) -1)
Ví dụ: để trích xuất văn bản giữa 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 tập hợp các dấu ngoặc kép khác được nhập vào. Dấu ngoặc kép đầu tiên được sử dụng để thoát khỏi ý nghĩa đặc biệt của câu trích dẫn thứ hai để “” ở giữa các dấu ngoặc kép ngoài cùng là viết tắt của một câu 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ách thức hoạt động của công thức này
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 để tìm kiếm và
- TÌM KIẾM (“” “”, 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 toán số ký tự cần trích xuất (num_chars):
Đầu tiên, chúng tôi tìm vị trí của câu 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 câu trích dẫn thứ 2 (trong A2 là 27), bạn trừ vị trí của câu trích dẫn thứ nhất (trong A2 là 10), sau đó trừ đi 1 để loại trừ chính câu 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ự
Có thể bạn đã biết, trong Microsoft Excel có hai hàm để tìm kiếm chuỗi: SEARCH (không phân biệt chữ hoa chữ thường) và FIND (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 một 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, chúng ta 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
Do đó, văn bản giữa “x” được trích xuất, không phải giữa “X” mà chúng tôi đang tìm kiếm:
Mặc dù chức năng FIND phân biệt chữ hoa chữ thường hoạt động rất đẹp:
=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ả 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 các hàm TEXTBEFORE và TEXTAFTER cùng nhau.
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 hiệu quả để 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 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ữ thường “x” và chữ hoa “X” là dấu phân cách:
=TEXTBEFORE(TEXTAFTER(A2, “x “, ,1), ” x”, ,1) +0
Cách thức hoạt động của công thức này
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 nó.
TEXTBEFORE(“Unexpected error)”, “)”)
Xong 🙂
Nhận văn bản giữa hai ký tự trong Google Trang tính
Để 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 tổ hợp TÌM KIẾM MID đượ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. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
