›10:52:48 24-11-2023 | Lượt xem: 974
Hướng dẫn này trình bày một phương pháp nhanh chóng, chính xác và không có lỗi để trích xuất các từ trong Excel mà không cần các công thức dài dòng và phức tạp nhờ vào một hàm tùy chỉnh.
Trong một trong những bài viết trước, chúng ta đã thảo luận về cách trích xuất từ đầu tiên, cuối cùng hoặc từ thứ N từ một ô bằng công thức MID. Bây giờ, sẽ chỉ cho bạn cách thay thế tất cả các công thức dài dòng đó chỉ bằng một hàm do người dùng xác định. Điều này sẽ giúp bạn tiết kiệm được rất nhiều thời gian và tránh được những sai sót vô ý khi xây dựng công thức.
Hàm ExtractWord tùy chỉnh được trình bày trong bài viết này sẽ cho phép bạn:
Trích xuất từ đầu tiên hoặc cuối cùng từ một ô.
Kéo từ thứ hai, thứ ba hoặc bất kỳ từ cụ thể nào từ một chuỗi.
Sử dụng bất kỳ dấu phân cách từ nào như dấu cách, dấu phẩy hoặc dấu chấm phẩy.
Nhận bất kỳ từ nào có chứa một ký tự được chỉ định từ văn bản.
Để bắt đầu, hãy thêm mã sau vào sổ làm việc của bạn như được mô tả trong Cách chèn mã VBA trong Excel.
Chức năng tùy chỉnh để trích xuất một từ từ ô Excel
Hàm ExtractWord(Data_range As Range, Word_num tùy chọn As Long , Dấu phân cách tùy chọn As String , Char As String tùy chọn ) As String Dim rCell As Range Dim text As String , sWord As String Dim arr() As String , arr2(10) As String , i As Long , a As Long ' nối phạm vi thành một chuỗi văn bản Cho mỗi rCell Trong Data_range text = text & " " & rCell Next rCell a = 1 ' dấu phân cách mặc định là khoảng trắng Nếu Dấu phân cách = "" Hoặc Dấu phân cách = " " Sau đó Delimiter = " " End If ' xóa khoảng trắng thừa text = WorksheetFunction.Trim(text) ' tạo một mảng các giá trị văn bản arr() = Split(text, Delimiter) ' trích xuất từ đầu tiên có chứa một ký tự cụ thể If Char <> "" Then Do While i < UBound (arr()) + 1 Nếu InStr(1, arr(i), Char, 1) > 0 Then arr2(a) = arr(i) a = a + 1 Kết thúc Nếu i = i + 1 Vòng lặp ' trích xuất từ chứa một ký tự cụ thể theo số Nếu Word_num > a - 1 Hoặc Word_num = -1 Then ExtractWord = arr2(a - 1) Ngược lại Nếu Word_num > 0 Thì Word_num = Word_num - 1 End Nếu ExtractWord = arr2(Word_num + 1) End If Else ' trích xuất từ If Word_num > UBound (Split(text, Delimiter)) + 1 Hoặc UBound (Split(text,Dấu phân cách)) = 0 Sau đó ExtractWord = "" Else ' trích xuất từ đầu tiên Nếu Word_num = 0 Then Word_num = 1 End Nếu ' trích xuất từ cuối cùng Nếu Word_num < 0 Then On Error Tiếp tục lại Word_num = UBound (Split(text, Delimiter)) + (Word_num + 2) End If ' trích xuất từ theo số ExtractWord = arr(Word_num - 1) End If End If End Hàm
Sau khi thêm mã của hàm vào Excel, bạn có thể sử dụng mã đó giống như bất kỳ hàm gốc nào khác. Nếu bạn mới làm quen với UDF và muốn tìm hiểu thêm, bạn có thể tìm thấy chi tiết đầy đủ trong Cách sử dụng các hàm tùy chỉnh trong Excel.
Cú pháp của hàm
Cú pháp sử dụng hàm tùy chỉnh như sau:
ExtractWord(dải_dữ liệu, [word_num], [dấu phân cách], [char])
Đối số bắt buộc:
Data_range – Đây là ô hoặc phạm vi ô mà bạn muốn trích xuất một từ.
Đối số tùy chọn:
Word_num – Đối số này chỉ định số thứ tự của từ bạn muốn trích xuất:
- Số dương – kéo một từ từ đầu chuỗi. Ví dụ: để trích xuất từ đầu tiên, hãy nhập 1 hoặc để trống.
- Số âm - kéo một từ từ cuối chuỗi. Ví dụ: để trích xuất từ cuối cùng, hãy nhập -1.
- Mặc định là 1 - nếu đối số bị bỏ qua, hàm sẽ trích xuất từ đầu tiên.
Dấu phân cách – Sử dụng đối số này để chỉ định dấu phân cách từ. Nếu không được chỉ định, giả định rằng các từ được phân tách bằng dấu cách. Dấu phân cách có phân biệt chữ hoa chữ thường. Các dấu phân cách liên tiếp được coi là một dấu phân cách duy nhất.
Char – Sử dụng đối số này khi bạn muốn trích xuất một từ có chứa một ký tự cụ thể. Nếu bạn cũng đặt đối số word_num thì hàm sẽ trích xuất từ đầu tiên, thứ hai, thứ ba, … hoặc từ cuối cùng chứa ký tự được chỉ định.
Với cú pháp đã thiết lập, hãy chuyển sang các ví dụ thực tế và xem xét kỹ hơn cách sử dụng hàm mạnh mẽ này trong bảng tính Excel của bạn.
Hãy bắt đầu với tình huống phổ biến nhất - trích xuất từ đầu tiên từ một ô Excel. Giả sử văn bản đích nằm trong ô A3, hàm tùy chỉnh sẽ đơn giản như sau:
=ExtractWord(A3, 1)
Trên thực tế, bạn có thể làm cho nó ngắn hơn nữa bằng cách bỏ qua đối số thứ hai vì word_num mặc định là 1:
=ExtractWord(A3)
Bây giờ, hãy so sánh công thức này với công thức MID truyền thống để trích xuất từ đầu tiên từ chuỗi:
=MID(A3, 1, SEARCH(" ", A3) - 1)
Như bạn có thể thấy, kết quả giống nhau nhưng chức năng tùy chỉnh ngắn gọn hơn và thân thiện với người dùng hơn.
Để trích xuất từ cuối cùng từ chuỗi văn bản trong Excel bằng hàm tùy chỉnh, hãy đặt đối số word_num thành -1. Đây là công thức trông như thế nào:
=ExtractWord(A3, -1)
Bây giờ, nếu bạn định thực hiện cùng một tác vụ bằng cách sử dụng các hàm Excel gốc, bạn cần tạo một công thức dài hơn và phức tạp hơn nhiều để kết hợp sáu hàm khác nhau. Đây là những gì nó sẽ trông như thế nào:
=TRIM(RIGHT(SUBSTITUTE(A3, " ", REPT(" ", LEN(A3))), LEN(A3)))
Bất chấp sự phức tạp của công thức gốc, kết quả hoàn toàn giống với những gì bạn nhận được với hàm tùy chỉnh:
Bằng cách chọn chức năng tùy chỉnh, bạn không chỉ đơn giản hóa các công thức Excel mà còn nâng cao độ rõ ràng và dễ đọc của bảng tính.
Để trích xuất bất kỳ từ cụ thể nào từ một chuỗi văn bản, bạn chỉ cần cho hàm ExtractWord biết bạn muốn từ nào.
Giả sử bạn muốn trích xuất từ thứ hai từ ô A3. Đây là công thức:
=ExtractWord(A3, 2)
Nếu mục tiêu của bạn là từ thứ hai tính từ cuối chuỗi (từ cuối cùng nhưng chỉ có một từ), công thức sẽ thay đổi thành:
=ExtractWord(A3, -2)
Ngoài ra, bạn có thể sử dụng cách tiếp cận linh hoạt hơn bằng cách đặt số từ vào một ô khác. Ví dụ: với số từ mục tiêu trong ô B3, công thức có dạng sau:
=ExtractWord(A3, B3)
Công thức này ngắn hơn và đơn giản hơn nhiều so với công thức sử dụng các hàm Excel thông thường, nhưng nó cho bạn kết quả tương tự:
=TRIM(MID(SUBSTITUTE(TRIM($A$3), " ", REPT(" ", LEN($A$3))), (B3-1)*LEN($A$3)+1, LEN($A$3)))
Để biết giải thích chi tiết về công thức này, hãy xem hàm MID để trích xuất từ thứ N từ văn bản.
Bằng cách sử dụng phương pháp này, bạn tiết kiệm được rất nhiều thời gian. Ngoài ra, rất khó mắc lỗi với hàm tùy chỉnh nhỏ gọn, trong khi các công thức dài hơn có thể dẫn đến các lỗi vô tình.
Trong tất cả các ví dụ trước, tập trung vào việc trích xuất các từ được phân tách bằng dấu cách. Nhưng điều đó không phải lúc nào cũng đúng. Khi bạn nhập dữ liệu vào Excel từ các chương trình khác, chúng có thể sử dụng nhiều ký hiệu khác nhau làm dấu phân cách như dấu chấm phẩy, dấu gạch chéo, thanh dọc, v.v. Trong những tình huống như vậy, đối số thứ ba trong hàm ExtractWord sẽ rất hữu ích.
Hãy minh họa điều này bằng một ví dụ. Giả sử bạn muốn lấy từ thứ hai từ ô A3, trong đó các từ được phân cách bằng " / " (dấu cách-dấu gạch chéo-dấu cách). Công thức trông như thế này:
=ExtractWord(A3, 2, " / ")
Điều quan trọng cần lưu ý là bao gồm ký tự khoảng trắng ở cả hai bên của biểu tượng dấu gạch chéo để tránh chiếm giữ khoảng trắng ở đầu và cuối trong kết quả. Nếu bản ghi nguồn của bạn sử dụng dấu gạch chéo không có dấu cách thì bạn chỉ cần sử dụng "/" cho đối số dấu phân cách .
Như được hiển thị trong ảnh chụp màn hình bên dưới, trong trường hợp này, kết quả là bạn thậm chí có thể nhận được nhiều từ. Điều này xảy ra vì khoảng trắng ở đây được coi là ký tự thông thường, không phải dấu phân cách. Nói cách khác, bất kỳ và tất cả ký tự giữa các dấu phân cách được chỉ định trong công thức đều được coi là một từ duy nhất.
Để trích xuất một từ chứa một ký tự cụ thể, bạn có thể sử dụng đối số thứ tư của hàm ExtractWord, được đặt tên phù hợp là char . Đối số này xác định ký tự cụ thể nào bạn muốn tìm trong văn bản và hướng dẫn hàm trích xuất từ chứa ký tự đó.
Ví dụ: tài liệu tài chính được tạo bằng Excel thường chứa nhiều số tiền khác nhau trong chuỗi văn bản. Thay vì sử dụng các biểu thức chính quy phức tạp để trích xuất những số liệu này, bạn có thể sử dụng hàm tùy chỉnh đơn giản.
=ExtractWord(A3, 1, , "$")
Trong trường hợp này, công thức trả về từ đầu tiên chứa ký hiệu "$" từ văn bản trong A3.
Ví dụ dưới đây minh họa cách bạn có thể sử dụng tham chiếu đến ô chứa ký tự đích (B3). Bạn cũng có thể chỉ định lần xuất hiện nào của từ có ký tự mong muốn sẽ trả về (ô A6:A9). Như được hiển thị, có bốn từ có ký hiệu "$" trong chuỗi nguồn. Bạn có thể truy xuất bất kỳ công thức nào trong số chúng bằng cách nhập công thức sau vào ô B6 và sao chép nó xuống ô B9:
=ExtractWord($A$3, A6, , $B$3)
Ngoài các ký tự, hàm ExtractWord còn vượt trội trong việc trích xuất các từ có chứa văn bản nhất định. Để đạt được điều này, chỉ cần cung cấp văn bản đích (chuỗi con) làm đối số char .
Ví dụ: để trích xuất địa chỉ email có tên miền "gmail", công thức như sau:
=ExtractWord(A3, , , "gmail")
Kết quả được hiển thị trong ảnh chụp màn hình bên dưới:
Để trích xuất văn bản theo sau một từ cụ thể, bạn cần thực hiện hai việc:
Cung cấp từ mục tiêu làm đối số phân cách.
Cung cấp 2 cho đối số word_number để cho biết rằng bạn muốn truy xuất từ sau dấu phân cách.
Đúng, nó đơn giản thế thôi!
Ví dụ: để kéo văn bản theo cụm từ "Mã lỗi", công thức sẽ như sau:
=ExtractWord(A3, 2, "Error code")
Việc sử dụng chức năng ExtractWord cụ thể này có thể không rõ ràng ngay lập tức, nhưng đó là một kỹ thuật mạnh mẽ. Bằng cách kết hợp hàm ROW hoặc COLUMN trong công thức ExtractWord, bạn có thể chia chuỗi văn bản thành các ô riêng biệt một cách hiệu quả theo chiều dọc hoặc chiều ngang.
Để chia văn bản trong A3 thành các hàng bằng dấu " / " làm dấu phân cách, công thức là:
=ExtractWord($A$3, ROW(A1), " / ")
Đặt công thức này vào ô B3, sau đó sao chép nó xuống cột. Bạn sẽ đạt được kết quả sau:
Khi bạn sao chép công thức xuống cột, tham chiếu tương đối đến ô A1 sẽ thay đổi thành A2, A3, v.v. Kết quả là hàm ROW(A1) trả về 1, ROW(A2) trả về 2, v.v. Điều này cho phép bạn trích xuất tuần tự các từ đầu tiên, thứ hai và tiếp theo vào các ô riêng biệt trong một cột nhất định.
Để đặt các từ vào các cột riêng biệt, hãy sử dụng hàm COLUMN thay vì ROW để tạo số cho đối số word_num :
=ExtractWord($A$3, COLUMN(A1), " / ")
Nhập công thức vào ô đầu tiên (A8), sau đó sao chép nó qua hàng. Nội dung ô ban đầu sẽ được chia thành từng cột riêng lẻ tương ứng.
Trong Excel 365, bạn có thể đạt được kết quả tương tự bằng cách sử dụng hàm TEXTSPLIT. Tuy nhiên, trong các phiên bản trước không có TEXTSPLIT, kỹ thuật này có thể đóng vai trò là giải pháp thay thế đáng tin cậy cho các công cụ tách ô hoặc công thức tách chuỗi của Excel.
Hàm ExtractWord không bị giới hạn trong việc trích xuất các từ từ các ô riêng lẻ; nó cũng có thể trích xuất các từ trong một phạm vi. Để thực hiện việc này, hãy chỉ định phạm vi ô làm đối số đầu tiên. Đây là một ví dụ:
=ExtractWord($A$3:$A$13, C3)
Ảnh chụp màn hình bên dưới cho thấy cách bạn có thể truy xuất bất kỳ từ nào trong một phạm vi bằng cách sử dụng số từ được chỉ định trong các ô được xác định trước (C3:C12 trong trường hợp này).
