Thoạt nhìn, một vấn đề đơn giản với một giải pháp không rõ ràng: trích xuất từ cuối cùng từ một dòng văn bản. Chà, hoặc, trong trường hợp chung, phân đoạn cuối cùng, được phân tách bằng một ký tự phân cách nhất định (dấu cách, dấu phẩy, v.v.) Nói cách khác, cần thực hiện tìm kiếm ngược (từ đầu đến đầu) trong chuỗi của một ký tự đã cho và sau đó trích xuất tất cả các ký tự ở bên phải của nó.
Hãy xem xét một số cách truyền thống để lựa chọn: công thức, macro và thông qua Power Query.
Phương pháp 1. Công thức
Để dễ hiểu bản chất và cơ học của công thức, chúng ta hãy bắt đầu một chút từ xa. Đầu tiên, chúng ta hãy tăng số khoảng cách giữa các từ trong văn bản nguồn của chúng ta lên, ví dụ: 20 phần. Bạn có thể làm điều này với chức năng thay thế. THAY THẾ (THAY THẾ) và chức năng lặp lại N-lần một ký tự đã cho - NÓI LẠI (REPT):
Bây giờ chúng ta cắt bỏ 20 ký tự từ cuối văn bản kết quả bằng cách sử dụng hàm QUYỀN (ĐÚNG):
Nó đang trở nên ấm hơn, phải không? Nó vẫn để loại bỏ các khoảng trống thừa bằng cách sử dụng hàm TRIM (CẮT) và vấn đề sẽ được giải quyết:
Trong phiên bản tiếng Anh, công thức của chúng tôi sẽ giống như sau:
= TRIM (RIGHT (SUBSTITUTE (A1; »«; REPT (»«; 20)); 20))
Tôi hy vọng rõ ràng rằng về nguyên tắc, không cần thiết phải chèn chính xác 20 dấu cách - bất kỳ số nào cũng được, miễn là nó lớn hơn độ dài của từ dài nhất trong văn bản nguồn.
Và nếu văn bản nguồn cần được chia không phải bằng dấu cách mà bằng một ký tự phân tách khác (ví dụ: bằng dấu phẩy), thì công thức của chúng tôi sẽ cần được sửa lại một chút:
Phương pháp 2. Hàm macro
Nhiệm vụ trích xuất từ hoặc đoạn cuối cùng từ văn bản cũng có thể được giải quyết bằng cách sử dụng macro, cụ thể là viết hàm tìm kiếm ngược trong Visual Basic sẽ thực hiện những gì chúng ta cần - tìm kiếm một chuỗi con đã cho trong một chuỗi theo hướng ngược lại - từ từ cuối đến đầu.
Nhấn phím tắt Khác+F11 hoặc nút Visual Basic chuyển hướng nhà phát triển (Nhà phát triển)để mở trình chỉnh sửa macro. Sau đó, thêm một mô-đun mới qua menu Chèn - Mô-đun và sao chép mã sau vào đó:
Hàm LastWord (txt As String, Tùy chọn delim As String = "", Tùy chọn n As Integer = 1) As String arFragment = Split (txt, delim) LastWord = arFragment (UBound (arFragment) - n + 1) End Function
Bây giờ bạn có thể lưu sổ làm việc (ở định dạng hỗ trợ macro!) Và sử dụng hàm đã tạo theo cú pháp sau:
= LastWord (txt; delim; n)
Ở đâu
- txt - ô có văn bản nguồn
- phân định - ký tự phân cách (mặc định - khoảng trắng)
- n - từ nào nên được trích xuất từ cuối (theo mặc định - từ đầu tiên từ cuối)
Với bất kỳ thay đổi nào trong văn bản nguồn trong tương lai, hàm macro của chúng tôi sẽ được tính toán lại nhanh chóng, giống như bất kỳ hàm trang tính Excel tiêu chuẩn nào.
Phương pháp 3. Truy vấn nguồn
Truy vấn nguồn là một tiện ích bổ sung miễn phí của Microsoft để nhập dữ liệu vào Excel từ hầu hết mọi nguồn và sau đó chuyển đổi dữ liệu đã tải xuống thành bất kỳ dạng nào. Sức mạnh và sự thú vị của phần bổ trợ này tuyệt vời đến nỗi Microsoft đã tích hợp tất cả các tính năng của nó vào Excel 2016 theo mặc định. Đối với Excel 2010-2013 Power Query có thể được tải xuống miễn phí từ đây.
Nhiệm vụ của chúng tôi là tách từ hoặc đoạn cuối cùng thông qua dấu phân tách đã cho bằng Power Query được giải quyết rất dễ dàng.
Đầu tiên, hãy biến bảng dữ liệu của chúng ta thành một bảng thông minh bằng cách sử dụng các phím tắt. Ctrl+T hoặc lệnh Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng):
Sau đó, chúng tôi tải “bảng thông minh” đã tạo vào Power Query bằng lệnh Từ bảng / phạm vi (Từ bảng / phạm vi) chuyển hướng Ngày (nếu bạn có Excel 2016) hoặc trên tab Truy vấn nguồn (nếu bạn có Excel 2010-2013):
Trong cửa sổ trình chỉnh sửa truy vấn mở ra, trên tab Chuyển đổi (Biến đổi) chọn một đội Cột Tách - Theo Dấu phân cách (Cột phân tách - Theo dấu phân cách) và sau đó nó vẫn để đặt ký tự phân tách và chọn tùy chọn Dấu phân cách ngoài cùng bên phảiđể cắt không phải tất cả các từ, mà chỉ cắt từ cuối cùng:
Sau khi nhấp vào OK từ cuối cùng sẽ được tách thành một cột mới. Có thể xóa cột đầu tiên không cần thiết bằng cách nhấp chuột phải vào tiêu đề của nó và chọn Hủy bỏ (Xóa bỏ). Bạn cũng có thể đổi tên cột còn lại trong tiêu đề bảng.
Kết quả có thể được tải trở lại trang tính bằng lệnh Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào…):
Và kết quả là chúng tôi nhận được:
Như thế này - rẻ và vui vẻ, không có công thức và macro, gần như không cần chạm vào bàn phím 🙂
Nếu danh sách ban đầu thay đổi trong tương lai, chỉ cần nhấp chuột phải hoặc sử dụng phím tắt là đủ Ctrl+Khác+F5 cập nhật yêu cầu của chúng tôi.
- Tách văn bản cố định thành các cột
- Phân tích cú pháp và phân tích cú pháp văn bản bằng biểu thức chính quy
- Trích xuất các từ đầu tiên từ văn bản bằng hàm SUBSTITUTE