Trích xuất nhiều từ trong một ô và đưa chúng vào một dòng hoặc cột là điều chúng ta có thể làm thông qua hàm REGEXEXTRACT trong Google Sheets.
Không cần giải thích mục đích của việc trích xuất văn bản này, vì mục đích có thể thay đổi tùy thuộc vào công việc của bạn. Tuy nhiên, dưới đây là một ví dụ về tình huống mà việc trích xuất từ có thể hữu ích.
Tôi muốn tìm kiếm trong một ô chứa danh sách tên quốc gia và trích xuất một số quốc gia nếu chúng có trong danh sách.
Dưới đây là những tên của một số quốc gia nổi tiếng mà blog Info Inspired có mặt mạnh.
Với sự giúp đỡ của hàm REGEXEXTRACT, tôi sẽ trích xuất tất cả các từ khớp với một câu, hoặc danh sách được phân tách bằng dấu phẩy, trong một ô trong Google Sheets.
Chúng ta có thể sử dụng công thức REGEXEXTRACT lồng nhau (như được hiển thị trong Hình 1 bên dưới) hoặc hàm REGEXEXTRACT dựa trên LAMBDA (như được hiển thị trong Hình 3 ở dưới cùng) để thực hiện điều này.
Công thức lồng nhau không phải lúc nào cũng lý tưởng vì nó có thể làm cho công thức dài và khó hiểu hơn. Vì vậy, bạn nhất định muốn thử công thức dựa trên LAMBDA thay vì công thức lồng nhau.
Các bước để trích xuất nhiều từ sử dụng REGEXEXTRACT trong Google Sheets
Cú pháp của hàm REGEXEXTRACT:
REGEXEXTRACT(text, regular_expression)Bạn có thể trích xuất một từ duy nhất từ một ô bằng cách sử dụng công thức REGEXEXTRACT. Ví dụ, giả sử ô A1 có danh sách các quốc gia sau:
India, Hoa Kỳ, Vương quốc Anh, Canada, Philippines, Úc, Pháp, Brasil, Hà Lan, Malaysia, Đức, Indonesia, Pakistan, Nam Phi, Tây Ban Nha, Singapore, Ý, Mexico, Thụy Điển, Peru, Israel, Hồng Kông, Việt Nam, Bangladesh, Nigeria
Nếu bạn muốn trích xuất tên quốc gia “Hồng Kông”, bạn có thể sử dụng công thức sau:
=REGEXEXTRACT(A1, "(?i)Hồng Kông")Nhưng đừng dừng lại ở đó! Bạn có thể làm cho công thức REGEX này hoạt động như một kiểm tra logic.
Điều đó có nghĩa là bạn có thể kiểm tra ô A1 xem có từ “Hồng Kông” hay không và trả về từ đó nếu tìm thấy, hoặc trả về một giá trị khác.
Loại kiểm tra này là cần thiết. Nếu không, hàm REGEXEXTRACT sẽ đôi khi trả về lỗi #NA.
Dưới đây là công thức đó, trong đó chúng ta sử dụng hàm IFERROR:
=IFERROR(REGEXEXTRACT(A1, "(?i)Hồng Kông"), "Không có")Vì vậy, luôn nhớ sử dụng hàm IFERROR hoặc IFNA kết hợp với REGEX trong các tình huống như vậy.
Bây giờ, chúng ta hãy quay trở lại chủ đề chính của chúng ta, đó là cách trích xuất nhiều từ sử dụng REGEXEXTRACT trong Google Sheets.
Ở đây, chúng ta có thể tuân theo hai phương pháp. Nếu số từ cần trích xuất giới hạn chỉ từ 2-3 từ, chúng ta có thể sử dụng công thức REGEXEXTRACT lồng nhau hoặc công thức dựa trên LAMBDA.
1. Công thức REGEXEXTRACT lồng nhau
Công thức sau trích xuất nhiều từ sử dụng REGEXEXTRACT theo cách lồng nhau, và các hàm được phân tách bằng dấu phẩy và đặt trong Dấu ngoặc nhọn.
=IFERROR({REGEXEXTRACT(A1, "(?i)Singapore"), REGEXEXTRACT(A1, "(?i)Ý"), REGEXEXTRACT(A1, "(?i)Brasil")})Kết quả sẽ như sau.
Nếu công thức tìm thấy sự không khớp nào, kết quả sẽ có một hoặc nhiều ô trống.
Ví dụ, nếu ô A1 không chứa từ “Ý”, thì ô D1 sẽ trống.
Trong công thức chính của chúng ta (vui lòng tham khảo Hình 1 ở trên), tôi chỉ đơn giản sử dụng hàm TRANSPOSE để trả về kết quả vào một cột.
=TRANSPOSE(IFERROR({REGEXEXTRACT(A1, "(?i)Singapore"), REGEXEXTRACT(A1, "(?i)Ý"), REGEXEXTRACT(A1, "(?i)Brasil")}))Bạn có thể sử dụng hàm QUERY để loại bỏ các ô trống và giới hạn số giá trị trả về thành một số cụ thể (n).
=QUERY(TRANSPOSE(IFERROR({REGEXEXTRACT(A1, "(?i)Singapore"), REGEXEXTRACT(A1, "(?i)Ý"), REGEXEXTRACT(A1, "(?i)Brasil")})),"Select * where Col1<>'', Limit 3")Ở đây, mệnh đề LIMIT trong phần cuối điều khiển số lượng mục được trả lại. Trong công thức trên, giới hạn là 3.
2. Trích xuất nhiều từ từ một ô trong Google Sheets bằng cách sử dụng công thức dựa trên LAMBDA
Cú pháp của hàm MAP:
MAP(array1, [array2, ...], lambda)Nếu bạn muốn trích xuất một số lượng lớn từ khớp với một ô, bạn không thể sử dụng công thức REGEXEXTRACT lồng nhau trong Google Sheets. Công thức sẽ trở nên quá lớn và khó quản lý.
Thay vào đó, bạn có thể sử dụng một combo MAP (hàm trợ giúp LAMBDA) và REGEXEXTRACT. Điều này cho phép bạn trích xuất nhiều từ khớp mà không cần tạo ra một công thức lớn và phức tạp.
=LET(range, A1, value,{"Singapore","Ý","Brasil"}, TOCOL(MAP(TOCOL(value,1), LAMBDA(r,REGEXEXTRACT(TEXTJOIN(" ",TRUE,range),"(?i)"&r))),3))Trong đó:
- Ô A1 chứa danh sách các từ cần trích xuất.
- Mảng {“Singapore”, “Ý”, “Brasil”} chứa các quốc gia (giá trị) cần khớp trong danh sách.
Để khớp với nhiều giá trị hơn, bạn có thể chèn chúng trong mảng (Dấu ngoặc nhọn) được phân tách bằng dấu phẩy.
Dưới đây là một ví dụ:
=LET(range, A1:A, value, C1:C, TOCOL(MAP(TOCOL(value,1), LAMBDA(r,REGEXEXTRACT(TEXTJOIN(" ",TRUE,range),"(?i)"&r))),3))
Đó là tất cả những gì tôi muốn nói về cách trích xuất nhiều từ sử dụng REGEXEXTRACT trong Google Sheets.
