Sử Dụng Flash Fill
Đây là câu hỏi thường gặp: cách trích xuất dữ liệu từ một cột sang định dạng khác hoặc nhiều cột hơn.
Chúng ta sẽ xem cách làm bằng tính năng Flash Fill trước. Flash Fill là tính năng mới thêm vào Microsoft Excel 2013 và các phiên bản sau.
Trong ví dụ dưới, cột A chứa danh sách tên và họ. Flash Fill luôn cần cột nguồn để hoạt động, ở đây là cột A chứa tên nhân viên. Flash Fill thường dùng trong ví dụ tạo địa chỉ email hoặc dữ liệu lặp lại theo công thức. Công thức TEXT cũng dùng nhiều cho việc tạo email. Có nhiều hàm TEXT từ phức tạp đến đơn giản. Bài viết có ví dụ Flash Fill tạo email, ví dụ công thức TEXT, và cập nhật gợi ý công thức TEXT từ người bình luận.
1) Muốn điền cột B bằng địa chỉ email nhân viên. Cách IT công ty tạo email: kết hợp tên và họ nhân viên với tên miền công ty.
2) Ô B5, ta phải cho Flash Fill một ví dụ mẫu trước, Flash Fill sẽ dùng làm mẫu để điền phần còn lại của cột. Nhập text:
admin@wsxdn.com
Sau đó nhấn CTRL-ENTER.
3) Sau CTRL-ENTER, Excel tự đổi text thành hyperlink.
4) Highlight toàn bộ vùng B5:B15 muốn điền email bằng Flash Fill, nhấn CTRL-E (phím tắt Flash Fill) hoặc với vùng highlight vào tab Data → click Flash Fill.
5) Phần còn lại của vùng được điền địa chỉ email cần thiết.
Xem Thêm: Tách Văn Bản Trong Excel Dùng Flash Fill
Sử Dụng Công Thức TEXT
Nếu dùng phiên bản Excel cũ hơn, không sao, ta làm cùng việc với công thức TEXT. Tuy nhiên hơi phức tạp hơn trong trường hợp này.
1) Xem text cột A để xác định hàm TEXT nào dùng cho công thức phức tạp.
2) Thấy có tên, dấu cách, rồi họ.
3) Xây dựng công thức từ đầu.
Đầu tiên, dùng hàm SEARCH tìm dấu cách. Ô B5 nhập:
=SEARCH(" ", A5, 1)
Nhấn CTRL-ENTER.
4) Kết quả 5: đối với Emma Smith, dấu cách là ký tự thứ 5. Kéo công thức xuống thấy vị trí dấu cách các nhân viên khác. Nicole Roberts: dấu cách ở ký tự thứ 7.
5) Dùng hàm LEFT trích tên: dựa trên vị trí dấu cách, lấy tất cả ký tự bên trái dấu cách để lấy tên.
Cần cho LEFT: chuỗi văn bản đầu vào, và số ký tự từ trái muốn trích.
Chọn ô A5 làm chuỗi, số ký tự trích = SEARCH(" ", A5, 1) - 1:
=LEFT(A5, SEARCH(" ", A5, 1) - 1)
Trừ 1 vì không muốn lấy dấu cách.
6) Nhấn CTRL-ENTER, kéo công thức xuống → lấy được tất cả tên.
7) Copy công thức bằng CTRL-C hai lần để mở Clipboard.
8) Công thức lưu lại để dùng sau. Xóa cột B, làm việc trích họ.
9) Biết vị trí dấu cách, dùng LEFT lấy tên, nên dùng RIGHT lấy họ. Xây công thức trích họ.
10) Cần cho RIGHT biết số ký tự trích từ phải. Biết vị trí dấu cách. Dùng LEN tính tổng ký tự chuỗi, trừ vị trí dấu cách (từ SEARCH).
Công thức: =LEN(A5) – SEARCH(" ", A5, 1)) → số ký tự sau dấu cách.
11) Dùng RIGHT kết hợp công thức trên trích họ:
Nhập: =RIGHT(A5, LEN(A5) – SEARCH(" ", A5, 1))
13) Copy công thức, xóa nội dung ô.
14) Gộp tất cả thành một công thức lớn, vì đã phân tích từng bước và copy từng phần vào Clipboard.
Xóa cột, ô B5 nhập:
= LEFT(A5, SEARCH(" ", A5, 1) – 1) & RIGHT(A5, LEN(A5) – SEARCH(" ", A5, 1)) & "@mycompany.com"
15) Kéo đúp chuột điền xuống → cột điền đầy email.
Gợi Ý Tuyệt Vời Từ Người Bình Luận Đạt Cùng Hiệu Quả:
Rahul Singh đóng góp công thức đơn giản hơn đạt hiệu quả tương tự:
Ô B5 nhập: =CONCATENATE(SUBSTITUTE(A5," ", ""),"@mycompany.com")
Karthik đóng góp công thức còn đơn giản hơn:
=SUBSTITUTE(A5," ","")&"@mycompany.com"
Xem Thêm: Cách Dùng Flash Fill Trong Excel Tách Dữ Liệu (4 Cách Nhanh)
File Làm Việc
Kết Luận
Flash Fill là tính năng mới tiện lợi trong Excel 2013 trở lên. Nó là AutoFill nâng cao, tiết kiệm thời gian. Tuy nhiên, cho phiên bản Excel cũ và trường hợp Flash Fill không hoạt động, công thức TEXT đến giải cứu. Dù phức tạp hơn khi tạo, không có ví dụ quá phức tạp mà chúng không giải quyết được. Hãy bình luận cho biết bạn thích dùng Flash Fill hay công thức TEXT để trích xuất/định dạng dữ liệu và tại sao.
Bài Viết Liên Quan
- Cách Tắt Flash Fill Trong Excel (2 Phương Pháp Dễ Dàng)
- [Đã Giải Quyết!] Flash Fill Không Hoạt Động Trong Excel (5 Nguyên Nhân Và Giải Pháp)
- Flash Fill Không Nhận Diện Mẫu Trong Excel (4 Nguyên Nhân Và Cách Sửa)